Working with Date and Time Functions in SQL Server


Date and time functions in SQL Server are essential for managing and manipulating temporal data. In this guide, we'll explore various date and time functions, how to use them, and common scenarios where they are helpful, using SQL code examples.


Getting the Current Date and Time

To retrieve the current date and time, you can use the 

GETDATE()
function:


-- Get the current date and time
SELECT GETDATE() AS CurrentDateTime;

Date and Time Functions

SQL Server provides a wide range of date and time functions for tasks like adding or subtracting time intervals, extracting date parts, and formatting dates. Here are some examples:


-- Add days to a date
SELECT DATEADD(DAY, 7, '2023-10-18') AS NewDate;
-- Get the year from a date
SELECT YEAR('2023-10-18') AS Year;
-- Format a date as a string
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy') AS FormattedDate;

Date Arithmetic

You can perform arithmetic operations with dates. For example, to find the difference between two dates:


-- Calculate the difference in days between two dates
SELECT DATEDIFF(DAY, '2023-10-10', '2023-10-18') AS DaysDifference;

Common Date and Time Scenarios

Date and time functions are useful in various scenarios, such as calculating age, determining weekdays, and working with historical data. Here's an example of calculating age:


-- Calculate age based on a birthdate
SELECT DATEDIFF(YEAR, '1990-01-15', GETDATE()) AS Age;

What's Next?

You've learned the basics of date and time functions in SQL Server. To become proficient, you can explore more advanced topics, including handling time zones, performing date ranges, and optimizing queries that involve date and time data.


Date and time functions are crucial for applications that need to work with temporal data effectively.