How to Query Date and Time in SQL Server

Working with date and time values in SQL Server is a common task. Whether you are retrieving the current date, performing date calculations, or formatting time values, SQL Server provides a rich set of functions to help you manipulate and query date and time data.

1. Using the GETDATE() Function

The GETDATE() function in SQL Server is used to return the current system date and time. This is especially useful when you need to store or display the current date and time.

SELECT GETDATE();

This will return the current date and time in the format YYYY-MM-DD HH:MM:SS.

2. Date Functions: DATEADD and DATEDIFF

SQL Server provides two functions, DATEADD() and DATEDIFF(), for performing calculations on date and time values.

DATEADD()

The DATEADD() function is used to add or subtract time from a date.

SELECT DATEADD(DAY, 5, GETDATE());

This will add 5 days to the current date.

DATEDIFF()

The DATEDIFF() function returns the difference between two dates in terms of a specified unit (such as days, months, or years).

SELECT DATEDIFF(DAY, '2024-01-01', GETDATE());

This will return the number of days between January 1, 2024, and the current date.

3. Date Formatting

SQL Server allows you to format date and time values using the FORMAT() function.

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss');

This will return the date and time in the format YYYY-MM-DD HH:MM:SS.

4. Handling NULL Dates

It’s important to handle NULL values in date columns effectively. SQL Server offers several methods for dealing with NULLs, such as using the ISNULL() function.

SELECT ISNULL(date_column, '1900-01-01') FROM your_table;

This will return '1900-01-01' if the date_column contains NULL.

Conclusion

SQL Server provides a wide variety of date and time functions that allow you to perform complex queries and calculations. Understanding how to work with these functions can enhance your ability to manage and analyze time-sensitive data effectively.