For an instructor lead, in-depth look at learning SQL click below.
The ability to manipulate and calculate dates is a vital part of data analysis and manipulation in SQL. It enables developers to perform a wide range of calculations, including age calculation, time span between two dates, grouping records by specific date parts like year, month, day and so on. This post will guide you through how to perform the most common date calculations in SQL.
Adding and Subtracting Dates
You can add or subtract days from a date in SQL using the DATEADD function. This function adds or substracts a specified time interval from a given date.
|
1 2 3 4 5 6 7 |
-- Adding 10 days to a date SELECT DATEADD(day, 10, '2022-01-01') AS NewDate; -- Subtracting 5 days from a date SELECT DATEADD(day, -5, '2022-01-01') AS NewDate; |
Calculating the Difference Between Two Dates
The DATEDIFF function in SQL is used to get the difference between two dates in terms of day, months, years, etc.
|
1 2 3 4 |
-- Finding the difference in days between two dates SELECT DATEDIFF(day, '2022-01-01', '2022-01-10') AS DateDifference; |
Extracting Date Part
You can extract the specific parts of a date like the day, month, or year with the DATEPART function in SQL.
|
1 2 3 4 5 6 7 8 9 10 |
-- Getting the year from a date SELECT DATEPART(year, '2022-01-01') AS Year; -- Getting the month from a date SELECT DATEPART(month, '2022-01-01') AS Month; -- Getting the day from a date SELECT DATEPART(day, '2022-01-01') AS Day; |
Conclusion
This tutorial is just a tip of the iceberg when it comes to date calculations in SQL. Other functions like GETDATE(), CURRENT_TIMESTAMP, DAY(), MONTH(), YEAR(), etc., also help manipulate date and time in various ways. The key is understanding your data and knowing which function to use in a given scenario.