Recently I have recieved email from Vivek Jamwal, which contains many useful SQL Server Date functions.
----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
Reference : Pinal Dave (https://blog.sqlauthority.com), Vivek Jamwal
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!