DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 144--------------------------------------------------
       Date Time Calculations
       By: srinivasma_exceldbp Date: February 6, 2015, 3:55 am
       ---------------------------------------------------------
       --** Date Time Calculations
       First Day of the Year
       --First Day of Last Year
       SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()) - 1 ,
       '19000101')
       AS [FIRST DAY OF LAST YEAR];
       GO
       --First Day of This Year
       SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()),
       '19000101')
       AS [FIRST DAY OF This YEAR];
       GO
       --First Day of Next Year
       SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()) + 1 ,
       '19000101')
       AS [FIRST DAY OF NEXT YEAR];
       GO
       Last Day of the Year
       --Last Day of Last Year
       SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101',
       GETDATE()), '19000101'))
       AS [LAST DAY OF This YEAR];
       GO
       --Last Day of This Year
       SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101',
       GETDATE()) + 1 , '19000101'))
       AS [LAST DAY OF This YEAR];
       GO
       --Last Day of Next Year
       SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101',
       GETDATE()) + 2 , '19000101'))
       AS [LAST DAY OF NEXT YEAR];
       GO
       First Day of the Month
       -- To Get First Day of Previous Month
       SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) -
       1, '19000101')
       AS [FIRST DAY Previous MONTH];
       GO
       -- To Get First Day of Current Month
       SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()),
       '19000101')
       AS [FIRST DAY CURRENT MONTH];
       GO
       -- To Get First Day of Next Month
       SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) +
       1, '19000101')
       AS [FIRST DAY NEXT MONTH];
       GO
       Last Day of the Month
       -- To Get Last Day of Previous Month
       SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101',
       GETDATE()), '19000101'))
       AS [LAST DAY Previous MONTH];
       GO
       -- To Get Last Day of This Month
       SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101',
       GETDATE()) + 1, '19000101'))
       AS [LAST DAY This MONTH];
       GO
       -- To Get Last Day of Next Month
       SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101',
       GETDATE()) + 2, '19000101'))
       AS [LAST DAY NEXT MONTH];
       GO
       Start of the Day
       -- To Get Midnight Yesterday
       SELECT DATEADD(d, -1, DATEDIFF(d, 0, GETDATE()))
       AS [Midnight Yesterday];
       -- To Get Midnight Today
       SELECT DATEADD(d, -0, DATEDIFF(d, 0, GETDATE()))
       AS [Midnight Today];
       -- To Get Midnight Tomorrow
       SELECT DATEADD(d, 1, DATEDIFF(d, 0, GETDATE()))
       AS [Midnight Tomorrow];
       Other Dates/Times of Interest
       --To Get 11:59:59 Yesterday
       SELECT DATEADD(ss, (60*60*24)-1, DATEADD(d, -1, DATEDIFF(d, 0,
       GETDATE())))
       AS [11:59:59 Yesterday];
       --To Get Noon Yesterday
       SELECT DATEADD(hh, 12, DATEADD(d, -1, DATEDIFF(d, 0,
       GETDATE())))
       AS [Noon Yesterday];
       --To Get 11:59:59:997 Yesterday
       SELECT DATEADD(ms, (1000*60*60*24)-2, DATEADD(d, -1, DATEDIFF(d,
       0, GETDATE())))
       AS [11:59:59.997 Yesterday];
       *****************************************************