How to Get Current Date and Time in SQL?
Managing date and time efficiently is crucial for any database system. SQL provides built-in functions to retrieve the current date and time, which are especially useful for applications involving logging, reporting, and auditing.
In this article, we will explain the three main SQL functions to fetch the current date and time: GETDATE(), CURRENT_TIMESTAMP(), and SYSDATETIME(). We will cover their syntax, use cases, and practical examples for clarity and ease of implementation.
SQL Get Current Date and Time
SQL provides several built-in functions to retrieve the current date and time, each tailored to specific needs. These functions ensure that developers can fetch date and time values seamlessly, aiding in tasks like record creation, updates, and system time tracking. The three main SQL functions used for this purpose are:
- SQL GETDATE() Function
- SQL CURRENT_TIMESTAMP() Function
- SQL SYSDATETIME() Function
1. SQL GETDATE() Function
The SQL GETDATE() function fetches the current database system date and time in the format 'YYYY-MM-DD hh:mm:ss.mmm'. It is one of the most commonly used functions in SQL Server to retrieve date-time values.
Syntax
SELECT GETDATE();
Example 1: Fetch Current Date and Time
In this example, we use the GetDate() function in SQL to get the current date and time from the system.
Query
SELECT GetDate() AS 'CurrentDATETime';Output
2024-12-12 14:55:33.453
Example 2: Fetch Only Time Part
To extract only the time part from the GETDATE() function, we use the CONVERT() function. Here, the CONVERT() function formats the output to include only the time portion (hh:mm:ss) of the GETDATE() result.
Query
SELECT CONVERT(VARCHAR(8), GETDATE(),108)'hh:mi:ss'Output
08:25:01
2. SQL CURRENT_TIMESTAMP() Function
The SQL CURRENT_TIMESTAMP() function is used to retrieve the current timestamp, which includes both the date and time. It provides results similar to the GETDATE() function but is ANSI SQL-compliant, making it more portable across different database systems
Syntax
CURRENT_TIMESTAMP
Example: Fetch Current Timestamp
In this example, we use the CURRENT_TIMESTAMP() function in SQL to get the current date and time. The CURRENT_TIMESTAMP function outputs the current date and time in the same format as GETDATE(), making it interchangeable in most cases.
Query
SELECT CURRENT_TIMESTAMP AS "CURRENTTIMESTAMP"; Output
2024-12-12 14:55:33.453
3. SQL SYSDATETIME() Function
The SQL SYSDATETIME() function retrieves the current system date and time with higher precision than GETDATE() or CURRENT_TIMESTAMP(). It provides fractional seconds with a greater level of accuracy, which is particularly useful for applications requiring precise time calculations We can fetch the TIME part from the DATE and TIME value returned from the SYSDATETIME() function as below:
Syntax
SYSDATETIME()
Example 1: Fetch Current Date and Time
In this example, we use the SYSDATETIME() function in SQL to get the current date and time. The SYSDATETIME() function provides the date and time with precision up to seven fractional seconds, making it ideal for high-precision requirements.
Query
SELECT SYSDATETIME() 'Current TIME using SYSDATETIME()'Output
2024-12-12 14:55:33.4531234
Example 2: Extract Only Time from SYSDATETIME()
We can extract only the time part from the SYSDATETIME() function using the FORMAT() function. The FORMAT() function is used to extract and display only the time component of the SYSDATETIME() function result.
Query:
SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss') AS 'PreciseTime';Output
14:55:33
Conclusion
Understanding and utilizing SQL's built-in functions for retrieving the current date and time is important for efficient database management. The GETDATE(), CURRENT_TIMESTAMP(), and SYSDATETIME() functions to handle different precision and compatibility needs, making them flexible tools for developers. By using these functions appropriately, we can enhance the functionality of our database applications, ensuring accurate and reliable time tracking.
