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

  • I’m needing to retrieve a count on a column from 8:45 PM the previous night to 9:45 PM of the current day. How can I accomplish this?

    Reply
  • Hi.. i am not that much experienced on SQl but will explain you how the DATEDIFF function works.
    We must pass 3 Arguments i.e., datepart, Source date and Destintion date.The source and destination dates are nothing but, from which date to which date we want to see the dateDiff.

    For Example1:SELECT DATEDIFF(yy, ’07/12/1987′, GETDATE())–OUTPUT: 25
    Will returns the No:of years from ’07/12/2012′ and to current year 2012.

    Example2: SELECT DATEDIFF(MM, ’07/12/2012′, GETDATE())–OUTPUT: 5
    Will returns the No:of days from the day of ’07/12/2012′ to ’16/12/2012′.

    So we must pass the Sorce and destination Dates.
    I am surprised, how the DateDiff function works correctly without passing Source date.
    So, can any one of you please explain?

    Reply
  • Hi mustanglyf, can you please share the table and it’s columns that you are trying to retrieve a count.

    Reply
  • Another Simple way to find the Last day of current month
    select day(Dateadd(dd,-day(GETDATE()),(DATEADD(mm,1,GETDATE()))))
    select day(Dateadd(dd,-day(’11/07/1987′),(DATEADD(mm,1,’11/07/1987′))))–OUTPUT-30
    select day(Dateadd(dd,-day(’12/07/1987′),(DATEADD(mm,1,’12/07/1987′))))–OUTPUT-31

    Reply
  • Not sure if this has happened to anyone else, but this web page is displaying the queries without the 0’s. If you copy/paste, you’ll get a syntax error. Add 0’s after the stray commas to correct it.

    Great post, by the way. I use it almost every week.

    Reply
  • hey hai, i have a query on Sql server 2005 using Adventure Works DB
    1)write a query to display the order date along with the sales order ID and territory name.The Order date should displayed in the dd/mm/yyyy format.
    2)write a query to display the orderID and territory name of the order where the month of order is may and the year is 2004.

    In How many forms we can write this 2 queries plz mention the numerics before the answer..

    Reply
  • Hi,

    How do we get the Start date of the Quarter for the Fiscal Year (April-March, Jun-May) dynamicaly

    Thanks

    Reply
    • SELECT
      dateadd(quarter,datediff(quarter,0,getdate()),0) as first_day_of_quarter,
      dateadd(quarter,datediff(quarter,0,getdate())+1,-1) as last_day_of_quarter

      Refer this for more information (Last section of blog post)

      Reply
  • How can I find the Quarter from the 1st day of the week? For example 12/31/2012 is the firstday of the week (Monday) , I want to derive that it is Q1 2013. How can I do that?

    Reply
  • navya krishna
    March 11, 2013 6:41 pm

    Hi madhivanan,
    thanks for your help.i need same lyk not day i need previous year and previous month.can any one suggest for thtt.

    Reply
  • how to get any format specific date from any table
    fro example if want to get name and time of joining date of persons in any employee table in order of date-month-year hh:mm

    Reply
  • Hi,
    I am trying to calculate the average time between a series of dates (actfinish) on work orders (wonum) in a maintenance system (Maximo) that match a specific PM Number (pmnum) with T-SQL. I ran across this code but am having a difficult time understanding it enough to work in a “select – where” clause of my system. Can you suggest anything?

    Code working with:
    ;WITH base AS
    (
    SELECT CustomerID,
    ROW_NUMBER() over (partition BY CustomerID ORDER BY CreationDate, OrderID) AS rn
    FROM [Order]
    )
    SELECT b1.CustomerID,
    AVG(DATEDIFF(DAY,b1.CreationDate, b2.CreationDate) )
    FROM base b1
    JOIN base b2
    ON b1.CustomerID=b2.CustomerID
    AND b2.rn =b1.rn+1
    GROUP BY b1.CustomerID

    MikeN

    Reply
  • Wissam S. Ismail
    April 5, 2013 2:55 pm

    Hi,

    try this:

    SELECT DATEADD(wk,DATEDIFF(wk,0,’2013-02-03′),0)

    it will give you a wrong result, how is the 1sr day of the week is greater than the selected date!!!

    Reply
  • Hello,
    I need to get the day “Monday” and not the date.
    For example I used:
    SELECT GETDATE(); this will give me the date, but how can I convert the date to give me the day (Monday) instead? What sql querry can I use?
    I’m new at this (student).
    Any help is greatly appreciated.

    Reply
  • Hi, I have query like:

    Our Quarter Starts in June, it says as Week 1

    The User Is Passing the value: Test_1304_wk5

    it means:

    Test: Some revision name,
    13 : Year,
    04 : Quarter,
    WK 5 : Week 5 in fourth quarter.

    Using this info i have get like: 201347 ( Total Week Id)

    2013 : Year
    47 : week id according to our Quarter.

    Presently we are in fourth quarter, and week 47 out of 52 weeks

    Is it Possible, please help me..

    Reply
  • The formulas for First Day of Last Week and Last Day of Last Week appear to bring back a week starting on Monday and ending on Sunday.
    How would you modify it to bring back a week starting on Sunday and ending on Saturday?

    And what is the significants of using 7 in the formulas?

    dateadd(wk,datediff(wk,7,getdate()),0)
    dateadd(wk,datediff(wk,7,getdate()),6)

    Reply
  • Hi,

    How I can find the right date of the same day but in the last year?
    For example today is 2014-10-01 Wednesday, I need a function to find the same Wednesday but in the last year, in this case the function results should be 2013-10-02.

    Thanks!

    Reply
  • Hi

    How do I set a computed field value for a date field from two other date fields
    or one date field and one int field.
    permissiondate date
    prodnleadtime int –
    prodnstart date – compute value as permissiondate + prodnleadtime
    How to do this?
    How to subtract two dates and set this as a date for another date field in the table?
    Can some one point me to right steps on calculation of dates in sqlserver (ssms)?

    Reply
  • I would like my end date to be 4 weeks prior to todays date?

    Reply
  • Team,

    I can not figure out why this work. I need to know for myself; please!

    DATEADD(dd, 6 – (@@DATEFIRST + 5 + DATEPART(dw, [DateFiledInTable])) % 7, [Date]) as week_ending_date_week_starts_on_monday

    Thanks in advance

    Reply
  • Very helpfull. Cool.

    Reply

Leave a Reply

Menu