Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: Dates

  1. #1
    Join Date
    Aug 2009
    Posts
    37

    Unanswered: Dates

    Hi

    How to get week start and end dates of a current week

    Thanks
    Ramu

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is considered "the first day of the week" is a system setting in SQL Server, but this code should give you the general idea:
    Code:
    select	dateadd(week, datediff(week, 0, getdate()), 0) as WeekStart
    select	dateadd(day, 6, dateadd(week, datediff(week, 0, getdate()), 0)) as WeekEnd
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2010
    Location
    Hyderabad
    Posts
    12
    select dateadd(week, datediff(week, 0, getdate()), 0) as WeekStart

    try for enddate

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    What is considered "the first day of the week" is a system setting in SQL Server
    and i'm getting a problem on that

    what a sad tale of woe i could tell you, if you cared, about how my SQL Express engine died on me this morning and i had to re-install it, causing me to lose yet again another 2 hours of my life today...

    anyhow, i've finally got SQL Express 2008 running again, and when i tried that formula for the first day of the week, it spit out 2010-04-19

    which was yesterday, a monday, and i like my weeks to start on sunday

    so i checked @@DATEFIRST and yes it was set to 7

    so then i ran SET DATEFIRST 7 just to be sure

    and i'm still getting Monday as the start of the week by that DATEADD/DATEDIFF method

    what gives?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Perhaps Sir Rudy you are aware of this, but I believe Baron Blindman's code is in fact independent of DATEFIRST.
    Quote Originally Posted by r937 View Post
    what a sad tale of woe i could tell you, if you cared
    I care Rudy - I have put the kettle on

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    independent of DATEFIRST???

    okay, then why is it spitting out 2010-04-19 for the start of this week? more to the point, why isn't it 2010-04-18?

    and i'm sorry, but BOL says
    The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
    and since the formula does in fact use DATEDIFF, i would suggest that the formula is ~not~ independent of DATEFIRST
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937 View Post
    i would suggest that the formula is ~not~ independent of DATEFIRST
    Suggest all you want Sir Rudy, the Codes Don' Lie

    Code:
    DECLARE    @i AS INT = 1
    
    WHILE @i/8 = 0
    BEGIN
            
        SET DATEFIRST @i
    
        SELECT    @@DATEFIRST
              , DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) AS WeekStart
    
        SELECT  @i+= 1
        
    END
    
    SET DATEFIRST 7

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    Suggest all you want
    i will, ta

    let me now suggest that your code demonstration has revealed one interesting fact and is mute upon another

    your code demonstrates that DATEFIRST has no effect on the result of the expression -- which, for the sake of this thread, let's call the "blindman formula for first day of this week" or simply "formula"

    your code demonstrates that the formula is not sensitive to changes in DATEFIRST, and your code is mute on whether changes in DATEFIRST actually have an effect on DATEPART (which i believe they still do)

    so your code basically proves that the formula is broken

    this week started on Sunday the 18th
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937 View Post
    your code is mute on whether changes in DATEFIRST actually have an effect on DATEPART (which i believe they still do)
    My code is mute on whether changes in DATEFIRST actually have an effect on DATEPART because "Baron von Blindman's formula for first day of this week" does not incorporate DATEPART. If my code is considered mute on all subjects not present in the "formula" then it might be considered mute on all subjects barring only a very small subset of all possible subjects, namely those that are present in the "formula".

    Something it shares with all code snippets, in point of fact.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm confused now. Am I being honored or denigrated?
    So sad when I cannot tell the difference.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A little of one, some of the other

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    ..."Baron von Blindman's formula for first day of this week" does not incorporate DATEPART.
    it does too

    right here, marked in red --

    select dateadd(week, datediff(week, 0, getdate()), 0) as WeekStart

    those are dateparts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Am I being honored or denigrated?
    only the former

    how sad when you cannot tell the difference

    so, blindman, what do ~you~ have to contribute on the reasons for your formula indicating that Monday the 19th is the start of this week, and that setting DATEFIRST doesn't seem to effect this?

    broken, right?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    i said it doesn't incorporate DATEPART. would it be easier if i included parantythese? DATEPART(). given your penchant for the lower case i thought by datepart you meant DATEPART().

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What about calendar schemes whose weeks start based on events (like the Chinese, Hebrew, and Moslem calendars)? Those are good tricks to deal with!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •