none
CURRENT WEEK SQL QUERY RRS feed

  • Question

  • I don't even want to ask as though i know becuase i don't. Im looking for a select statement that grabs current week information based on a date column.  I don't want to pre-set a date range because i want this query to "ROLL" form week to week. Beginning of the week being Sunday and End of the week being Saturday.

    Below is as far as i could get.  But basicaly i would like to  WHERE DATENAME BETWEEN 'SUNDAY' AND 'SATURDAY'

    Sorry, im a beginner and i know this is no where near close. Please Help

    SELECT SALES_DATE, DATENAME(WEEKDAY, SALES_DATE)
    FROM SALES_TBL
    WHERE SALES_DATE BETWEEN

    Saturday, May 26, 2012 8:11 PM

Answers

  • SELECT SALES_DATE, DATENAME(WEEKDAY, SALES_DATE) FROM SALES_TBL WHERE SALES_DATE > = cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date) Basically selects all data from last sunday to current date.


    Chuck Pedretti | Magenic – North Region | magenic.com


    This will work to find the if your user's default language and date format are for the US (where the first date of the week is considered to be Sunday).  For most of the rest of the world, cast(dateadd(day,1-datepart(dw,getdate()), getdate()) as date) will return Monday.  To see this, try running

    set language French
    select 'French', cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)
    set language British
    select 'British', cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)
    set language English
    select 'US English', cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)

    Even if your only deal currently with US users, it is a good habit to use code which is independent of the date format used by the user.  One way to do that would be to pick a date that you know is a Sunday, (like Dec 31, 1899, but you could use any other date that is a Sunday, and then do

    SELECT SALES_DATE, DATENAME(WEEKDAY, SALES_DATE)
    FROM SALES_TBL
    WHERE SALES_DATE >= DATEADD(week, DATEDIFF(week, '18991231', getdate()), '18991231')
      AND SALES_DATE < DATEADD(week, 1 + DATEDIFF(week, '18991231', getdate()), '18991231')

    Tom

    • Proposed as answer by onpntModerator Saturday, May 26, 2012 10:16 PM
    • Marked as answer by uknowmeim Saturday, May 26, 2012 10:19 PM
    Saturday, May 26, 2012 9:31 PM
  • Using this code: http://www.sqlusa.com/bestpractices2005/lastsunday/

    SELECT SALES_DATE, DATENAME(WEEKDAY, SALES_DATE)
    FROM SALES_TBL
    WHERE SALES_DATE > = cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)
    Basically selects all data from last sunday to current date.


    Chuck Pedretti | Magenic – North Region | magenic.com


    • Edited by Chuck Pedretti Saturday, May 26, 2012 8:24 PM
    • Marked as answer by uknowmeim Saturday, May 26, 2012 10:20 PM
    Saturday, May 26, 2012 8:24 PM

All replies

  • Using this code: http://www.sqlusa.com/bestpractices2005/lastsunday/

    SELECT SALES_DATE, DATENAME(WEEKDAY, SALES_DATE)
    FROM SALES_TBL
    WHERE SALES_DATE > = cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)
    Basically selects all data from last sunday to current date.


    Chuck Pedretti | Magenic – North Region | magenic.com


    • Edited by Chuck Pedretti Saturday, May 26, 2012 8:24 PM
    • Marked as answer by uknowmeim Saturday, May 26, 2012 10:20 PM
    Saturday, May 26, 2012 8:24 PM
  • SELECT SALES_DATE, DATENAME(WEEKDAY, SALES_DATE) FROM SALES_TBL WHERE SALES_DATE > = cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date) Basically selects all data from last sunday to current date.


    Chuck Pedretti | Magenic – North Region | magenic.com


    This will work to find the if your user's default language and date format are for the US (where the first date of the week is considered to be Sunday).  For most of the rest of the world, cast(dateadd(day,1-datepart(dw,getdate()), getdate()) as date) will return Monday.  To see this, try running

    set language French
    select 'French', cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)
    set language British
    select 'British', cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)
    set language English
    select 'US English', cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)

    Even if your only deal currently with US users, it is a good habit to use code which is independent of the date format used by the user.  One way to do that would be to pick a date that you know is a Sunday, (like Dec 31, 1899, but you could use any other date that is a Sunday, and then do

    SELECT SALES_DATE, DATENAME(WEEKDAY, SALES_DATE)
    FROM SALES_TBL
    WHERE SALES_DATE >= DATEADD(week, DATEDIFF(week, '18991231', getdate()), '18991231')
      AND SALES_DATE < DATEADD(week, 1 + DATEDIFF(week, '18991231', getdate()), '18991231')

    Tom

    • Proposed as answer by onpntModerator Saturday, May 26, 2012 10:16 PM
    • Marked as answer by uknowmeim Saturday, May 26, 2012 10:19 PM
    Saturday, May 26, 2012 9:31 PM
  • This code works great! But if you wouldn't mind explaining a little bit of it. For the most part i understand most of it BUT....

    cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)

    if we could breat down this part.

    1st Why do we have to cast() it?

    2nd  the interval part of the DATEADD() , the 1 minus the datepart(dw, getdate()) what is going on here?

    3rd Where does Sunday come into play?

    Saturday, May 26, 2012 10:12 PM
  • This is a great Best Practice....one question for you what do the numbers 18991231 stand for?

    Saturday, May 26, 2012 10:14 PM
  • This code works great! But if you wouldn't mind explaining a little bit of it. For the most part i understand most of it BUT....

    cast(dateadd(day,1-datepart(dw, getdate()), getdate()) as date)

    if we could breat down this part.

    1st Why do we have to cast() it?

    2nd  the interval part of the DATEADD() , the 1 minus the datepart(dw, getdate()) what is going on here?

    3rd Where does Sunday come into play?

    1) because if you don't cast it as date then you will get a start datetime that includes a time which would exclude anything that happened Sunday before that time.

    2) Its taking the current value of weekday (1-7) and then subtracting 1 less than that many days to find sunday.  Assuming we are in the US  if the current dw is 2(monday) then it takes 1-2 (-1) and adds that to the current date making it sunday.

    3) The 1- day of the week is what gives you sunday (in the US as Tom points out)


    Chuck Pedretti | Magenic – North Region | magenic.com

    Saturday, May 26, 2012 10:31 PM
  • This is a great Best Practice....one question for you what do the numbers 18991231 stand for?


    Dec 31st 1899 - a day that is known to be a Sunday - regardless of what country you are in

    Chuck Pedretti | Magenic – North Region | magenic.com


    Saturday, May 26, 2012 10:31 PM

  • Dec 31st 1899 - a day that is known to be a Sunday - regardless of what country you are in



    Where did you find this information and are there other that are just as relevent?
    Saturday, May 26, 2012 10:59 PM
  • If you are specifying a date as a string, you should always use yyyymmdd format.  Other formats might be treated as a different date depending on the country you are in.  For example, '02/10/2011'  is February 10, 2011 in the US, but is October 2 in most other countries.  So, since I wanted Dec 31, 1899, that's why I used '18991231'  because that will be Dec 31, 1899 in every country.

    Tom

    Sunday, May 27, 2012 2:18 AM
  • Look up the ISO-8601 Standard for dates. We use a calendar table rather than doing temporal computations over and over. The format for week-within-year is [0-9][0-9][0-9][0-9]W[0-5][0-9]-[1-7]. The last digit is the day of the week . The two digits after the 'W' is the week number, 1 to 53. The first four digits are the year. 



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, May 27, 2012 4:05 PM
  • You sire are a life saver. Thank you!
    Friday, March 21, 2014 6:41 PM