Introduction
Introduction Statistics Contact Development Disclaimer Help
Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
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];
*****************************************************
You are viewing proxied material from gopher.createaforum.com. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.