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.

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

• http://www.w3schools.com/sql/func_dateadd.asp
• http://www.w3schools.com/sql/func_datediff.asp

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.

About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN