(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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]; *****************************************************