Aug 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

SQL SERVER – Few Useful DateTime Functions to Find Specific Dates

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
112 Comments

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

SQL DateTime, SQL Function, SQL Scripts
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

  • Mysoor TechTeam
    August 23, 2009 10:43 pm

    Great Article and BEST SITE!!! You are hero!

    Reply
  • Victor
    October 2, 2009 3:12 am

    Hi, I always read your posts, but this one was the best.

    Thanks

    Reply
  • Shahaji Udar
    October 14, 2009 3:58 pm

    Hi,

    Its really very helpful information.
    Thanks a lot.

    It saves lot of development&/RD work.

    Thanks Again !!!

    Shahaji Udar

    Pune(India)

    Reply
  • John Lambert
    February 18, 2010 10:20 pm

    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.

    Reply
  • Jeff
    February 24, 2010 5:45 am

    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

    Reply
  • Sanjeev Puri
    June 3, 2010 3:05 pm

    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.

    Reply
    • madhivanan
      July 5, 2011 8:05 pm

      Yes you can use but make sure to use unambigious format YYYYMMDD. Read this for more information

      Reply
  • Sanjeev Puri
    June 3, 2010 3:08 pm

    I want to calculate DayOfQuarter for the given date. Any idea, how we can do it?

    Thanks,
    Sanjeev Puri

    Reply
    • bhumi Dev
      October 15, 2010 5:06 pm

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

      Reply
  • Maggie
    October 2, 2010 12:55 am

    Awesome – exactly what I was looking for! Thanks

    Reply
  • Jeff Muzzy
    October 4, 2010 11:03 pm

    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

    */

    Reply
  • Raj
    November 8, 2010 9:16 am

    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.

    Reply
    • madhivanan
      November 8, 2010 1:50 pm

      Post table structure, sample data and expected result

      Reply
      • Raj
        November 10, 2010 4:42 am

        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.

      • madhivanan
        November 11, 2010 2:00 pm

        where datecol>=dateadd(month,-1,@monthyear) and datecol<dateadd(month,0,@monthyear)

  • raj.
    November 11, 2010 10:36 pm

    Hi Madhivanan,

    Thanks for your reply, hope it solves my issue.

    Thanks,
    Raj.

    Reply
  • Prawin
    November 14, 2010 10:23 pm

    Thanks a lot for this article. Was very helpful in writing some specific dates.

    Reply
  • Sue
    January 4, 2011 7:00 am

    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.

    Reply
  • Siju
    January 11, 2011 1:29 pm

    Hi , I want to get start and end date for each month in a given year what can i do for this? Help me to complete this task…

    Reply
    • madhivanan
      January 11, 2011 4:36 pm

      start with this

      select dateadd(month,datediff(month,0,getdate()),0) as first_day,dateadd(month,datediff(month,-1,getdate()),-1) as last_day

      Also refer this

      Reply
      • Siju
        January 12, 2011 12:33 pm

        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…

  • Siju
    January 12, 2011 11:20 am

    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…

    Reply
    • madhivanan
      January 14, 2011 5:42 pm

      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

      Reply
  • Eden
    January 18, 2011 12:00 pm

    Really helpful date formats….all in one
    Thanks

    Reply
  • Shibu P
    March 12, 2011 2:44 pm

    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

    Reply
  • Khanh
    March 18, 2011 5:35 am

    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!

    Reply
    • madhivanan
      June 1, 2011 2:21 pm

      The examples posted in this blog post will show you how to use that way in the where clause

      Reply
  • Brian
    May 26, 2011 12:18 am

    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

    Reply
  • madhivanan
    June 1, 2011 2:20 pm

    The following will also help you

    Reply
« Older Comments Newer Comments »

Leave a Reply

Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.

Pinal has authored 14 SQL Server database books and 94 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.

Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.

Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).

Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.

Comprehensive Database Performance Health Check

Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.

Once you learn my business secrets, you will fix the majority of problems in the future.

SQL Server Performance Tuning Practical Workshop

Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.

Essentially I share my business secrets to optimize SQL Server performance.