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 (http://blog.SQLAuthority.com),

About these ads

99 thoughts on “SQL SERVER – Few Useful DateTime Functions to Find Specific Dates

  1. –First Day Of Current Quarter
    SELECT Dateadd(qq, Datediff(qq,0,GetDate()), 0)

    –Last Day Of Current Quarter
    SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate())+1, 0))

    –First Day Of Prior Quarter
    SELECT Dateadd(qq, Datediff(qq,0,GetDate())-1, 0)

    –Last Day Of Prior Quarter
    SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate()), 0))

  2. Great info indeed (and your blog in general is full of ressources).
    For some reporting, I need the first and last days of previous month (those I got from your useful dateTime functions) but also the first and last days of 2 months ago as well as the first and last days of 3 months… what ever is the day of today (getdate()). Any hint?

  3. When it comes to processing dates, one thing I failed to see online is the calculation for Retail Calendar. I’m in the Retail industry and calendar is different from the Gregorian calendar. Any clue in developing a Retail Calendar other then creating a custom calendar table?
    Thanks,
    Chris

  4. Hi Dave,

    Thank you for all you have given, all your efforts, all your sharing and goodness to so many.

    I pray great blessings will come your way for all those you have helped.

    RSJ

  5. i want to know the query of the month difference between two date as specific month name in sql server 2005! r u help me .

  6. I\’d like to know how to get three months before today so that it changes every day that I open up MS SQL Server 2005. Does anyone know?

  7. Hi,

    Its really very helpful information.
    Thanks a lot.

    It saves lot of development&/RD work.

    Thanks Again !!!

    Shahaji Udar

    Pune(India)

  8. Actually, GETDATE alone (the first item you have listed) is not equivalent to ‘Today’ but to ‘Now’ because it includes the time.

    This would be a problem in cases such as if you want a where clause that only includes records before today.

    In SQL Server 2008, you can take advantage of the Date data type to get a ‘Today’ function with today’s current date, but no time value (equal to a time of 00:00:00) like this:

    CAST(GETDATE() as DATE)

    For SQL Server 2005 and below, you have to go to more trouble to extract the year, month, and day and compare them all.

    Note that in SQL Server Reporting Services, there is a TODAY function, but naturally, it only works in SSRS.

  9. You can just create a function called DateOnly()

    create function [dbo].[DateOnly](@DateTime DateTime)
    — Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
    returns datetime
    as
    begin
    return dateadd(dd,0, datediff(dd,0,@DateTime))
    end

    GO

  10. Hi,

    Thanks for posting the useful script.
    But, the script can be easily understood if we replace 0 by ’01/01/1900′. Still, its very useful piece of code.

    Thanks,
    Sanjeev Puri.

    • –First Day Of Current Quarter
      SELECT Dateadd(qq, Datediff(qq,0,GetDate()), 0)

      –Last Day Of Current Quarter
      SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate())+1, 0))

      –First Day Of Prior Quarter
      SELECT Dateadd(qq, Datediff(qq,0,GetDate())-1, 0)

      –Last Day Of Prior Quarter
      SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate()), 0))

  11. Here is the answer for the question for the person who wanted to know what the day in the quarter was. I didn’t see this until this morning…

    Have a good day…

    jeff

    ——–

    /* — function part commented out so you can test.

    CREATE FUNCTION [dbo].[NumberOfDaysInCurrentQuarter] ( @dtInput DATETIME )
    RETURNS INT
    BEGIN
    */

    DECLARE @dtStartOfQuarter DATETIME
    DECLARE @nDaysInQuarter int;
    — you don’t need the following three lines if you make this a function
    — these are just so you can run in the management studio to test.
    declare @dtInput datetime;
    set @dtInput = GETDATE();

    SET @dtStartOfQuarter = CAST(YEAR(@dtInput) AS VARCHAR(4)) + ‘-‘ +
    CAST(DATEPART(Q, @dtInput) * 3 – 2 AS VARCHAR(2)) + ‘-01′

    print ‘@dtStartOfQuarter = ‘ + convert(varchar, @dtStartOfQuarter, 121);

    set @nDaysInQuarter = DATEDIFF(day, @dtStartOfQuarter, @dtInput);

    print ‘@nDaysInQuarter = ‘ + cast(@nDaysInQuarter as varchar(4));

    /*
    RETURN @nDaysInQuarter ;

    END
    GO

    */

  12. HI,

    Can you help me to write a code for calculating previous month value and previous 6 months values? I have monthyear object and month object in universe and db is sql server 2008.

    Thanks,
    Raj.

      • Hi Madhivanan,

        Thanks for your reply.

        I have a monthyear object on which i wnat to apply a condition to get previous month data and also previous 6 months data dynamically. The date format is like 1/1/2009. Data in the db need not be upto date so i cant use curent date or sysdate. I have to write a condition using this to create those filters. I also have another object callled monthyear-char. it has values like apr 2010. I can use either of objects to create those filters.

        Suppose i have data until aug 2010 then if i use previous month condition, then july 2010 data should display.

        I appreciate if you can help me in this regard.

        Db is sql server 2008.

        Thanks,
        Raj.

  13. Can you please help with a SQL query for returning the year, then month, date from a table that has datetime values like this: 12/10/2010 and 11/7/2008. My customer wants to search the database using a specific date and that portion works, however he also wants to search by all the recoreds in the database and return all the dates of surgery in chronological order – latest dates first. So it would return 12/31/2010 first and 11/7/2008 last.

    I used and surgDate>=dateadd(month,-1,@monthyear) and surgDate<dateadd(month,0,@monthyear and indeed it returns all the dates in chrono order starting with the most recent year…so this part works fine. However, when I allow the user to provide a specific date, the query returns nothing. I am using AND surgDate LIKE '%#(surgDate)#%' to determine if there are any dates "LIKE" the one the user has provided in the form…but I understand that this does not work the same as strings…any suggestion would be appreciated. Thank you.

  14. Thanks for ur reply … This query for only one month but i want the results for 12 months for a given year.. what can i do for this…

    • select
      dateadd(month,number,dateadd(month,datediff(month,0,getdate()),0)) as first_day,
      dateadd(month,number,dateadd(month,datediff(month,-1,getdate()),-1)) as last_day
      from master..spt_values
      where type=’p’ and number between 0 and 11

  15. Friends, as we start our week on a “Sat” and end on a “Fri”

    SET DATEFIRST 6
    SELECT GETDATE() CurrentDate,
    DATEADD(dd,(DATEPART(dw, GETDATE()) – 1) * – 1, GETDATE()) firstDayOfWeek,
    DATEADD(dd, (7 – DATEPART(dw,GETDATE())), GETDATE()) lastDayOfWeek

    Regards

  16. Hi,

    To get the EventDate for last month I used

    SELECT * From SomeTable
    Where EventDate Between DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AND DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

    Now users want only to see the EventDate between hours from 4pm to 6am of every day of last month. Can i please have some hints?

    Thanks in advance!

  17. Hi folks, wow, just came across this great site.

    I have a bunch of dates where i need to find the Appropriate Quarter for each date and Appropriate Year so i can create a Pivot Grid.

    Any help appreciated.
    Brian

  18. Product Sales Report
    ID Pro_Name Pro_Cost No. Of Units Bill Pro_Cat_ ID Date
    1 Nokia C7 18000.00 1 18000.00 5 5/22/2010
    2 Iron Box 2000.00 1 2000.00 4 1/31/2010
    3 Dell Laptop 30000.00 1 30000.00 3 7/18/2010
    4 Rice 600.00 4 2400.00 1 9/28/2010
    5 HP Printer 5000.00 1 5000.00 3 11/26/2010
    6 Horlicks 150.00 1 150.00 1 2/15/2010
    7 HP Laptop 30000.00 1 30000.00 3 7/18/2010

    Product Category Table

    Pro_Cat_ ID Pro_Cat_Name
    1 Home
    2 Business
    3 Computers
    4 Electronics
    5 Mobiles
    Expected output

    Quarter Home Business Computers Electronics Mobiles
    Q1 – – – 2,000.00 –
    Q2 150 – – – 18,000.00
    Q3 2,400.00 – 60,000.00 – –
    Q4 – – 5,000.00 – –

    How can i do that?

  19. Hi Sure,
    (Brian, I think this might answer you pivot question as well).

    You question seems to be way off topic. What you are looking for is not a date function but take a look at this articles about pivot tables, dynamic columns, and one or two other methods to implement what you are looking for. Basically you are trying to do group by
    cast(year(dtSale) as varchar(5))+’-Q’
    + cast(datepart(qq, dtSale) as varchar)
    which give you the format YYYY-Qn

    My solution uses computed columns, and give you a running total for the quarter. The quarter is a bit messy to give you YYYY-Qn format but it works.
    Standard disclaimer applies this hasn’t been tested against a large sample size for query performance, blah, blah.

    Here is a the code to what you want. Again I would do research on what you trying to do with pivot tables.

    FYI,
    jeff
    ——

    — drop table #prodTest;
    create table #prodTest
    (
    id int identity(1,1),
    stProduct_name varchar(150) NOT NULL,
    stProduct_catagory varchar(80) NOT NULL,
    mnPriceUnit money,
    nQuantity int,
    — computed column
    mnPriceSale as (nQuantity * mnPriceUnit),
    dtSale datetime NOT NULL default getdate(),
    — use money cause in your example it would get to big too quick with decimal
    );

    /*
    — using sql one insert to multiple rows.
    — if you are using sql 2005 then do the select union all to
    — make it work
    */

    — drop table #prodTest
    insert into #prodTest
    (
    stProduct_name,
    stProduct_catagory,
    mnPriceUnit,
    nQuantity,
    dtSale
    )

    VALUES
    (‘Nokia C7′, 5, 18000.00, 1, ‘5/22/2010′),
    (‘Iron Box’, 4, 2000.00, 1, ‘1/31/2010′),
    (‘Dell Laptop’, 3, 30000.00, 1, ‘7/18/2010′),
    (‘Rice’, 1, 600.00, 4, ‘9/28/2010′),
    (‘5 HP Printer’,3, 5000.00, 3, ’11/26/2010′),
    (‘Horlicks’, 1, 150.00, 1, ‘2/15/2010′),
    (‘HP Laptop’, 3, 30000.00, 1, ‘7/18/2010′);

    SELECT * FROM #prodTest order by dtSale desc;

    select
    — this gets your YYYY-Qn Format

    cast(year(dtSale) as varchar(5))+’-Q’
    + cast(datepart(qq, dtSale) as varchar) as [Year_Quarter],

    — now create a table you want
    sum(case when stProduct_catagory=’1′ then mnPriceSale else 0 end) as [Home],
    sum(case when stProduct_catagory=’2′ then mnPriceSale else 0 end) as [Business],
    sum(case when stProduct_catagory=’3′ then mnPriceSale else 0 end) as [Computers],
    sum(case when stProduct_catagory=’4′ then mnPriceSale else 0 end) as [Electronics],
    sum(case when stProduct_catagory=’5′ then mnPriceSale else 0 end) as [Mobiles],
    sum(mnPriceSale) as [Quarter_Total]
    from
    #prodTest
    group by
    cast(year(dtSale) as varchar(5))+’-Q’
    + cast(datepart(qq, dtSale) as varchar)
    order by
    Year_Quarter desc;

  20. SELECT DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())) will give me at midnight last night.

    If I would like to have the time set-up to be 11:30 p.m., (23 hours ahead), what SQL statement should I use?

    Thanks.

  21. how to query–
    businessDate = 1 Business day prior to the 10th of each month
    if date is Tuesday 1/10 then businessdate=1/9 which is monday
    but if date is sunday 1/10,sunday is not business day so prior to business day,bsiness date=thursday.(prior to busness day friday)

  22. I liked the ideas in this post. Just another question. My client start the new month on the 26th day. e.g. 26 December 2011 = 1 January 2012 = first day of month January 2012.
    Do anyone know a quick way to calculate these dates.
    What I intend doing is to have a date lookup table where the ID is 20111226, the date is 26/12/2011 and the financial date is 1 January 2012.
    I need to populate the table from 1 Jan 2000 to 31 Dec 2050

    Any ideas?

  23. Just wondering how to incorporate a month end split. I want to use the weeks as listed above, but if the ‘Wednesday’ is the end of the month, I want to see that day instead of the last day of the week (instead of Saturday). My groups are already set, now I am just working on the labels, which is where my problem is occuring.
    thanks in advance

  24. Hi Pinal..

    Is there any way to retrieve records using the IN clause for datetime datatype.

    for eg: Select * from table where date IN (‘2012-03-04′,’2012-03-05′,..)

    RJP

  25. I’m trying to run these in SS 2008. I’m not getting accurate results. It’s 1 day off. I copied them exactly & it’s showing the 1st day of the week as 4/2 instead of 4/1 & the same for the last day as it’s showing off by 1 as well.

    I need to get totals for the full prior week Sun-Sat for a report that can be run anytime. I’m trying to figure out how to code for that. Help?

  26. here is a more esoteric way to convert a datetime to a date only: I also think it uses the least sql resources:

    declare @MyDate datetime
    select @MyDate = GETDATE()

    select convert(datetime,floor(convert(float, @MyDate)))

  27. Hello sir.. Above are really good information .But i have single doubt about datediff() funtion. select DATEDIFF (wk,0,getdate()) . it returns some integer value . how it works .please tell me sir

  28. 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?

  29. 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?

  30. 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

  31. 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.

  32. 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..

  33. 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?

  34. 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.

  35. 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

  36. 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

  37. 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.

  38. 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..

  39. 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)

  40. 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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s