SQL SERVER – Few Useful DateTime Functions to Find Specific Dates

Recently I have recieved email from

----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),

Solarwinds
, ,
Previous Post
SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
Next Post
SQL SERVER – FIX : ERROR : Could Not Connect to SQL Server – TDSSNIClient initialization failed with error 0x7e, status code 0x60

Related Posts

112 Comments. Leave new

  • Blog is nice. This blog is really well and daily I m seeing the bolg.

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Is there a way to get EPI week of a date?

    Reply
  • ——-
    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.

    Reply
  • EOMONTH (Transact-SQL) 2012 server.

    Reply
  • 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

    Reply
  • 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

    Reply
  • Michelle Ashton
    July 5, 2018 9:50 pm

    As always, you make my life so much better. Thanks for sharing!

    Reply

Leave a Reply

Menu