Recently I have recieved email from Vivek Jamwal, which contains many useful SQL Server Date functions. I am sharing those useful DateTime functions here.
----Today SELECT GETDATE() 'Today' ----Yesterday SELECT DATEADD(d,-1,GETDATE()) 'Yesterday' ----First Day of Current Week SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week' ----Last Day of Current Week SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week' ----First Day of Last Week SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week' ----Last Day of Last Week SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week' ----First Day of Current Month SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month' ----Last Day of Current Month SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month' ----First Day of Last Month SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month' ----Last Day of Last Month SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month' ----First Day of Current Year SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year' ----Last Day of Current Year SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year' ----First Day of Last Year SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year' ----Last Day of Last Year SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'
ResultSet:
Today
———————–
2008-08-29 21:54:58.967
Yesterday
———————–
2008-08-28 21:54:58.967
First Day of Current Week
————————-
2008-08-25 00:00:00.000
Last Day of Current Week
————————
2008-08-31 00:00:00.000
First Day of Last Week
———————–
2008-08-18 00:00:00.000
Last Day of Last Week
———————–
2008-08-24 00:00:00.000
First Day of Current Month
————————–
2008-08-01 00:00:00.000
Last Day of Current Month
————————-
2008-08-31 23:59:59.997
First Day of Last Month
———————–
2008-07-01 00:00:00.000
Last Day of Last Month
———————–
2008-07-31 23:59:59.997
First Day of Current Year
————————-
2008-01-01 00:00:00.000
Last Day of Current Year
————————
2008-12-31 23:59:59.997
First Day of Last Year
———————–
2007-01-01 00:00:00.000
Last Day of Last Year
———————–
2007-12-31 23:59:59.997
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
112 Comments. Leave new
Blog is nice. This blog is really well and daily I m seeing the bolg.
Nice post. I would like to know the query for half year start and end date EX: 01/01/2016 – 30/06/2016
01/07/2016 – 31/12/2016
Thanks in advance
Hello Friends…
I need help in calculating the below date metrics using TSQL,
Week to date-Lastweek-Current Year
Week to Week-Lastweek-Current Year
Week to date-Current Week-Current year
Week to Week-Lastweek-Last Year
Week to date-Current Week-Last year
Month to Month-Last Month-Current Year
Month to date-Current Month-Current Year
Month to Week-current Month-Current Year
Month to date -Last Month-Current Year
Month to week -lastmonth-current year
Month to month Last month-last year
Our week start from Sunday & ends on a Saturday.
Thanks for the help.
Is there a way to get EPI week of a date?
——-
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) ‘Last Day of Last Month’
——-
This appears to be incorrect, at least it didnt result as ‘Laast Day of Last Month) use EOMonth(GETDATE(), -1) is alternative solution.
EOMONTH (Transact-SQL) 2012 server.
how to get specified day date in a month..
for eg; if day is monday then want to display all monday dates in a month
hello ,
Please i need your help , i need to recuperate this month and the two previous month
I use this SQL request:
WHERE datepart(YYYY,dTime)=
datepart(YYYY,GETDATE( )) and datepart(m,dTime) in (datepart(m,GETDATE( )),datepart(m,(dateadd(m,-1,GETDATE( )))),datepart(m,(dateadd(m,-2,GETDATE( )))));
but i have a problem when I ‘m in Junarry ( i this case I need acctual and previous year )
i traied to use also this SQL request to resolve my issue it work but probably there are a problem because it don’t give me the same number of output row :
convert(date,dTime) in (convert(date,(dateadd(m,GETDATE( )))),convert(date,(dateadd(m,-1,GETDATE( )))),convert(date,(dateadd(m,-2,GETDATE( )))))
can you help me please to found the good request ?
thanks
As always, you make my life so much better. Thanks for sharing!