SQL SERVER# : Get last possible time for a DAY.

During one of SQL requirement, was trying to form Range of Years for executing a DML in small batches as per YEAR.
But due to missing last possible end time, we were not able to manipulate some records.

For instance check below SQL that we were initially trying to form Year Range,

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS START_OD_YEAR,
DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) + 1, -1) AS END_OF_YEAR

Output ::
START_OD_YEAR END_OF_YEAR
----------------------- -----------------------
2017-01-01 00:00:00.000 2017-12-31 00:00:00.000

For END_OF_YEAR we are only getting last day of year, but not till last possible time for end date of year.

Using DATEADD for milliseconds (ms), we can achieve last possible end of time instead for any hard-code.
lets walk trough some example to understand it better.

SELECT DATEADD(MS, -2,GETDATE()) AS MS_2, GETDATE() AS SYS_DATE

MS_2                          SYS_DATE
-----------------------       -----------------------
2017-06-02 16:24:15.577 2017-06-02 16:24:15.580

It should minus 2 milliseconds from current system time but somehow shows as minus by 3 factor. (Not sure why)
For getting last day of year till last time possible, we will follow below pseudo logic.

GET_FIRST_DAY_OF_NEXT_YEAR – (2 MILLISECONDS TO GET LAST TIME POSSIBLE FOR PREVIOUS YEAR)

lets create the SQL to implement same.

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS START_OD_YEAR,
DATEADD(MS,-2,DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) + 1, 0)) AS END_OF_YEAR

START_OD_YEAR END_OF_YEAR
----------------------- -----------------------
2017-01-01 00:00:00.000 2017-12-31 23:59:59.997

Hope it was informative!

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL, SQL Server Learning and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s