
Getting first and last second of the current year, month and day
Using minimum and maximum date time in SQL query
Probably the most common error developers make in queries is dealing with date ranges and most of the time it is because of missing the minimum and maximum time of the specific date.
Month
To calculate minimum time value is pretty easy. Basically any date value you use will return minimum date which is 00:00:00, like for example
SELECT CAST('2015-07-20' AS DATETIME)
will return value 2015-07-20 00:00:00.000 which is minimum time, start of the day actually. In case you want to do this dynamically for the current month you can use DATEDIFF and DATEADD SQL Server built in functions.
I usually add 1 second to the start date to avid exactly midnight time
SELECT DATEADD(s, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
So As I mentioned, getting the minimum time of the date is easy, but calculating the last time of current month is a bit tricky. Basically you need to get minimum time of the next day and reduce 1 second. This way you will get time 23:59:59 for the last day of the current moth.
SELECT DATEADD(s, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))
Year
The same approach can be used to calculate minimum and maximum time for the current year
SELECT DATEADD(s, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) SELECT DATEADD(s, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))
Day
In case you need time span of one day only for the current date same approach can be used
SELECT DATEADD(s, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) SELECT DATEADD(s, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0))
References
Disclaimer
Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.
Comments for this article