When working with date types in SQL Server, it is often necessary to remove the time portion and focus only on the date. There are several ways to achieve this, but in this article, we will explore my favorite method – the DATEADD/DATEDIFF (referred to as DADD) method.
For a long time, I struggled to remember the syntax and parameters of the DADD method, which made it difficult for me to use it consistently. However, once I took the time to understand how it works, it became much easier to remember and apply. In this article, I will explain the concept behind the method and provide useful examples of how to use it effectively.
Let’s start with a common scenario – stripping the time portion off a date or the current date and time (GETDATE()). The DADD method for achieving this is:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
For example, if today is February 27, 2010, and the current time is 17:31:42.670, the result of the above query would be: 2010-02-27 00:00:00.000
Now, let’s break down the DADD method. The inner DATEDIFF portion calculates the number of days between 0 (which represents January 1, 1900, when cast as a date in SQL) and the current date. In this case, the result is 40234. The second portion of the equation adds this number of days to 0, effectively removing the time portion and returning the start of the day.
The same concept can be applied to calculate the first day of the week, month, or year:
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) -- First day of the week.
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0) -- First day of the month.
SELECT DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0) -- First day of the year.
You can also add or subtract a specific number of days by modifying the dateadd portion of the query:
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) -- Start of the day 2 days from now.
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2) -- Start of the day 2 days ago.
If you want to add or subtract a week or month, you can do so by adding the desired value directly after the datediff portion:
SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()) + 2, 0) -- Start of the month 2 months from now.
It’s important to note that the DADD method handles overflow correctly. For example, adding a month to January 31 would result in February:
SELECT DATEADD(mm, DATEDIFF(mm,0,'20100131') + 1, 0) -- Start of next month.
You can also use the “first” theory to find the “last” of something else. For instance, to find the last day of the prior month, you can start with the first day of the month and subtract a day:
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)) -- Last day of the prior month.
When dealing with time intervals smaller than a day, such as milliseconds, you can find the “end” of a day by using the appropriate units (milliseconds for SQL Server 2000/2005 or microseconds/nanoseconds for SQL Server 2008):
SELECT DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) -- End of the previous day (datetime).
SELECT DATEADD(ns, -100, CAST(DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0) as datetime2)) -- End of the previous day (datetime2).
It’s worth noting that the datetime data type in SQL Server is only accurate to 3 milliseconds, while datetime2 can be accurate to 100 nanoseconds. If you’re using datetime, you need to cast or convert the value before using milliseconds or nanoseconds with dateadd.
Finally, it is recommended to avoid using the “end” of a day whenever possible. For example, when querying for all values on a specific date, it is better to use >= the start of the day and < the start of the next day. This approach ensures accuracy and avoids potential data loss if the datetime data type becomes more accurate in the future.
In conclusion, the DATEADD/DATEDIFF method is a powerful tool for manipulating dates in SQL Server. By understanding its concept and syntax, you can easily remove the time portion from dates or perform various time calculations. Remember to consider the accuracy limitations of the datetime data type and use the appropriate units for smaller time intervals. Happy coding!