SQL Server Journey with SQL Authority

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 28 Comments

28 Responses

1. on January 5, 2008 at 12:25 am | Reply Chris Bowie

   Your articles are very helpful. Thanks for the public service.

   
   
2. on January 16, 2008 at 4:35 pm | Reply Hemaraj

   Your solutions are extremely useful. Thanks a ton brother

   
   
3. on February 13, 2009 at 9:59 am | Reply Srinivasan

   Dear Pinal Dev…

   I have never found a simpler scripts in date functions any where in the world … Hats offf….

   
   
4. on May 8, 2009 at 8:40 pm | Reply Dan Richardson

   simple, to-the-point answers – perfect every time.
   Thanks a bunch! :)

   
   
5. on May 14, 2009 at 8:05 pm | Reply Dave M.

   Awesome! Perfect solution as always! Many thanks…..

   
   
6. on June 19, 2009 at 6:45 pm | Reply Marius

   This does not work. Here is an example:

   DECLARE @TODAY DATETIME
   SET @TODAY = ‘June 14, 2009′

   SELECT

   @TODAY AS TODAY,
   LAST_MONDAY = DATEADD(wk, DATEDIFF(wk,0,@TODAY), 0)

   :-)

   
   
7. on July 15, 2009 at 3:44 pm | Reply Ziplodocus

   I believe that is because Sunday is classed as the first day of the week.

   Therefore when the date is a Sunday, the Monday of the current week is tomorow.

   :)

   
   
8. on August 28, 2009 at 1:00 am | Reply cimnil

   How to find Monday of last week?

   
   
9. on August 28, 2009 at 5:41 pm | Reply Brian Tkatch

   @cimnil

   Find Monday of this week , and use DATEADD to subtract 7 days.

   
   
10. on September 29, 2009 at 3:23 am | Reply vijaianand

    Perfect job. Thanks

    
    
    • on November 10, 2009 at 6:52 pm | Reply ColSchmoll

      “I believe that is because Sunday is classed as the first day of the week.”

      If I set Monday to be the first day of the week it still doesn’t work.

      SET DATEFIRST 1;
      DECLARE @TODAY DATETIME ;
      SET @TODAY = ‘June 14, 2009′ ;
      SELECT
      @TODAY AS TODAY,
      LAST_MONDAY = DATEADD(wk, DATEDIFF(wk,0,@TODAY), 0)

      returns:

      TODAY LAST_MONDAY
      2009-06-14 00:00:00.000 2009-06-15 00:00:00.000

      
      
11. on November 20, 2009 at 1:31 am | Reply gus

    Try this:
    DATEADD(wk, DATEDIFF(d, 0, GETDATE()) / 7, 0).

    This works because 1/1/1900 (i.e. ’0′) was a Monday so you are basically dividing the days since then by 7 days exactly.

    FYI – you can also find the day of the week based on a Monday start date (i.e. Monday = 0, Tuesday = 1, etc.) by using “% 7″ instead of “/ 7″.

    
    
    • on January 30, 2013 at 1:40 pm | Reply Eman

      i would like to find Saturday of this week.. do u have any idea?

      
      
12. on March 10, 2010 at 9:55 pm | Reply DBA100

    The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
    doesn’t return the date of last monday.

    I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.

    what if I want to run a script in any time and the result must return the date of last monday, last tuesday til last friday.

    Any way to do it?

    
    
13. on March 10, 2010 at 9:56 pm | Reply DBA100

    The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
    doesn’t return the date of last monday.

    I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.

    Any query can give me the date of last monday til last friday, this mean I need 5 days no matter what day I run the script.

    
    
14. on July 6, 2010 at 11:23 pm | Reply Jim

    Pinal Dave – Thanks SO much – I have seen your posts before but this one gave me exactly what I wanted. and so simple… a summary for the week beginning on monday was MUCH simpler with this small bit of code! you are the BEST!

    
    
15. on September 3, 2010 at 8:31 am | Reply erwin

    nice piece of script. simple yet effective!

    
    
16. on March 2, 2011 at 4:33 am | Reply GS

    I tried to find Wednesday of the current week on the same lines, but was not successful. Can you pl. help.
    For any given date, I need the Wednesday of that week.

    Thanks in advance,
    GS

    
    
    • on March 3, 2011 at 1:54 pm | Reply madhivanan

      Try this

      select dateadd(week,datediff(week,0,getdate()),0)+2

      
      
17. on August 18, 2011 at 2:53 am | Reply Nagesh

    Hi There,

    Dave’s tit bit is perfect for defalut date caliculations. i.e. by defauls Sunday is considerded as first day of week in US.

    if you want to consider monday as first day of the week you may use the below tit bit.

    SET DATEFIRST 1
    select (DateAdd(d, – (DatePart(dw,’2011-08-21′) -1), ’2011-08-21′))

    
    
18. on August 25, 2011 at 8:55 pm | Reply DBWhisperer

    Since you helped me out here, I’ll pass along what it was exactly you helped me with. This query builds a result set that lists all the weeks in the past year for a view or function:

    WITH CTE_PastYearinWeeks
    AS (
    select
    /* Thanks to SQL Authority for below. It gets the number of weeks between now and the beginning of (sql) time and adds it to the beginning of (sql) time, thus setting it to the first day of the week! */
    DATEADD(wk, DATEDIFF(wk,0,GETDATE()-365), 0) as DateStart
    UNION ALL
    SELECT DATEADD(WK,1,DateStart)
    FROM CTE_PastYearinWeeks
    WHERE DATEADD(WK,1,DateStart) <= GETDATE()
    –and DATEPART(YYYY,DateStart) <= DATEPART(YYYY,GETDATE())
    )
    select DATEPART(YYYY,DateStart) as YearNum
    , DATEPART(WK,DateStart) as WeekNum
    , SUBSTRING(CONVERT(varchar, DateStart, 107),1,6) + ' – ' + CONVERT(varchar, DATEADD(DD,6,DateStart), 107) as WeekTitle
    from CTE_PastYearinWeeks
    OPTION (MAXRECURSION 0);

    
    
19. on January 30, 2012 at 3:07 pm | Reply gobi

    how to find next week your birthday alert using sql

    
    
20. on February 29, 2012 at 2:02 pm | Reply AJ

    You ROCK !!!!!! A Big Thumbs Up

    
    
21. on May 17, 2012 at 12:13 am | Reply kevin

    I need to find MondayOfCurrentWeek for last year

    
    
22. on June 5, 2012 at 9:27 pm | Reply kjes

    I need to find Sunday, three months ago.

    
    
23. on August 15, 2012 at 1:03 am | Reply Steve

    Thanks! Worked fine.

    
    
24. on January 29, 2013 at 5:44 pm | Reply Eman

    Hi there,
    i would like to find Saturday of this week.. do u have any idea?

    
    
    • on April 16, 2013 at 10:52 am | Reply Rajeshwar

      select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) — Saturday of the current week

      
      

Leave a Reply Cancel reply

Enter your comment here...

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

Email (required) (Address never made public)

Name (required)

Website

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

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

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

Cancel

Connecting to %s

Notify me of follow-up comments via email.

Notify me of new posts via email.