+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Get date for last year for same day and day of week..

  1. Get date for last year for same day and day of week..

    So if i run the following :

    select datepart(wk,getdate()) -- return 4th week of the year 2008

    select datepart(dw,getdate()) -- returns 1st day of the week i.e. Sunday



    What I want to do is get the equivalent 4th week of 2007 and the 1st Sunday
    of that week..

    Thank



  2. Re: Get date for last year for same day and day of week..

    Hassan, try a calendar table. They are very useful for this type of
    problem.

    http://www.aspfaq.com/2519


    "Hassan" wrote in message
    news:elP0Of9WIHA.4476@TK2MSFTNGP06.phx.gbl...
    > So if i run the following :
    >
    > select datepart(wk,getdate()) -- return 4th week of the year 2008
    >
    > select datepart(dw,getdate()) -- returns 1st day of the week i.e. Sunday
    >
    >
    >
    > What I want to do is get the equivalent 4th week of 2007 and the 1st
    > Sunday of that week..
    >
    > Thank
    >
    >



  3. Re: Get date for last year for same day and day of week..

    You also need to make sure you are clear on what you mean by the '4th week'.
    Is it the 4th full week, the fourth week that has at least 3 days in it, or
    week that starts with the fourth Sunday (or something else)???

    --
    HTH
    Kalen Delaney, SQL Server MVP
    www.InsideSQLServer.com
    http://blog.kalendelaney.com


    "Aaron Bertrand [SQL Server]" wrote in message
    news:OB9imF%23WIHA.2268@TK2MSFTNGP02.phx.gbl...
    > Hassan, try a calendar table. They are very useful for this type of
    > problem.
    >
    > http://www.aspfaq.com/2519
    >
    >
    > "Hassan" wrote in message
    > news:elP0Of9WIHA.4476@TK2MSFTNGP06.phx.gbl...
    >> So if i run the following :
    >>
    >> select datepart(wk,getdate()) -- return 4th week of the year 2008
    >>
    >> select datepart(dw,getdate()) -- returns 1st day of the week i.e. Sunday
    >>
    >>
    >>
    >> What I want to do is get the equivalent 4th week of 2007 and the 1st
    >> Sunday of that week..
    >>
    >> Thank
    >>
    >>

    >




  4. Re: Get date for last year for same day and day of week..

    In my calendar year, Jan 1 is always week 1.

    So I want to go to week 4 of last year and get the first Sunday of that
    week.


    "Kalen Delaney" wrote in message
    news:eRyCYH%23WIHA.4828@TK2MSFTNGP05.phx.gbl...
    > You also need to make sure you are clear on what you mean by the '4th
    > week'. Is it the 4th full week, the fourth week that has at least 3 days
    > in it, or week that starts with the fourth Sunday (or something else)???
    >
    > --
    > HTH
    > Kalen Delaney, SQL Server MVP
    > www.InsideSQLServer.com
    > http://blog.kalendelaney.com
    >
    >
    > "Aaron Bertrand [SQL Server]" wrote in message
    > news:OB9imF%23WIHA.2268@TK2MSFTNGP02.phx.gbl...
    >> Hassan, try a calendar table. They are very useful for this type of
    >> problem.
    >>
    >> http://www.aspfaq.com/2519
    >>
    >>
    >> "Hassan" wrote in message
    >> news:elP0Of9WIHA.4476@TK2MSFTNGP06.phx.gbl...
    >>> So if i run the following :
    >>>
    >>> select datepart(wk,getdate()) -- return 4th week of the year 2008
    >>>
    >>> select datepart(dw,getdate()) -- returns 1st day of the week i.e. Sunday
    >>>
    >>>
    >>>
    >>> What I want to do is get the equivalent 4th week of 2007 and the 1st
    >>> Sunday of that week..
    >>>
    >>> Thank
    >>>
    >>>

    >>

    >
    >




  5. Re: Get date for last year for same day and day of week..

    > You also need to make sure you are clear on what you mean by the '4th
    > week'. Is it the 4th full week, the fourth week that has at least 3 days
    > in it, or week that starts with the fourth Sunday (or something else)???


    Well, you can add a column to your calendar table called WeekNumber, and
    define it however you like. :-)


  6. Re: Get date for last year for same day and day of week..

    Did you read the article Hassan? Or do you just want us to do it for you
    (again)? Sorry to be rude, but you ask a lot of questions that you could
    solve yourself (often quicker) by reading the references people point you
    to, instead of just asking for more help and evidently ignoring that a link
    was posted in the first place. :-(


    "Hassan" wrote in message
    news:u$C4uM%23WIHA.5816@TK2MSFTNGP06.phx.gbl...
    > In my calendar year, Jan 1 is always week 1.
    >
    > So I want to go to week 4 of last year and get the first Sunday of that
    > week.



  7. Re: Get date for last year for same day and day of week..

    Yes, I was just going to suggest that could be part of a calendar table. I
    wanted to make sure Hassan was aware that there is no fixed definition for
    week number.

    Thanks!

    --
    Kalen Delaney, SQL Server MVP
    www.InsideSQLServer.com
    http://blog.kalendelaney.com


    "Aaron Bertrand [SQL Server]" wrote in message
    news:e8uCKa%23WIHA.3556@TK2MSFTNGP02.phx.gbl...
    >> You also need to make sure you are clear on what you mean by the '4th
    >> week'. Is it the 4th full week, the fourth week that has at least 3 days
    >> in it, or week that starts with the fourth Sunday (or something else)???

    >
    > Well, you can add a column to your calendar table called WeekNumber, and
    > define it however you like. :-)




  8. Re: Get date for last year for same day and day of week..

    I agree.. Sorry about that..

    "Aaron Bertrand [SQL Server]" wrote in message
    news:e8mp5a%23WIHA.1132@TK2MSFTNGP06.phx.gbl...
    > Did you read the article Hassan? Or do you just want us to do it for you
    > (again)? Sorry to be rude, but you ask a lot of questions that you could
    > solve yourself (often quicker) by reading the references people point you
    > to, instead of just asking for more help and evidently ignoring that a
    > link was posted in the first place. :-(
    >
    >
    > "Hassan" wrote in message
    > news:u$C4uM%23WIHA.5816@TK2MSFTNGP06.phx.gbl...
    >> In my calendar year, Jan 1 is always week 1.
    >>
    >> So I want to go to week 4 of last year and get the first Sunday of that
    >> week.

    >




  9. Re: Get date for last year for same day and day of week..

    Honest doesn't equal rude in my book Aaron. :)

    --
    Kevin G. Boles
    Indicium Resources, Inc.
    SQL Server MVP
    kgboles a earthlink dt net


    "Aaron Bertrand [SQL Server]" wrote in message
    news:e8mp5a%23WIHA.1132@TK2MSFTNGP06.phx.gbl...
    > Did you read the article Hassan? Or do you just want us to do it for you
    > (again)? Sorry to be rude, but you ask a lot of questions that you could
    > solve yourself (often quicker) by reading the references people point you
    > to, instead of just asking for more help and evidently ignoring that a
    > link was posted in the first place. :-(
    >
    >
    > "Hassan" wrote in message
    > news:u$C4uM%23WIHA.5816@TK2MSFTNGP06.phx.gbl...
    >> In my calendar year, Jan 1 is always week 1.
    >>
    >> So I want to go to week 4 of last year and get the first Sunday of that
    >> week.

    >




  10. Re: Get date for last year for same day and day of week..

    I completely agree with Aaron that Calendar table is the good solution to
    this problem (Hey, when is SQL Server getting a Calendar table as a standard
    feature? Analysis Services already gets one created by the Date Template).

    Here is an alternative approach that calculates the corresponding week and
    day on the fly. Since 1 Gregorian calendar year = 52 weeks + 1 day (2 days
    in a leap year), then a Gregorian year has 52.1775 weeks. The DATEADD
    function in SQL Server adds by intervals based on the datepart, and for week
    it will add weeks (not days) and the math will produce the same day of the
    week. Then a formula to calculate the same week and day a year back will
    look like this:

    SELECT DATEPART(wk, DATEADD(week, ROUND(52.1775 * -1, 0),
    CURRENT_TIMESTAMP))
    SELECT DATEPART(dw, DATEADD(week, ROUND(52.1775 * -1, 0),
    CURRENT_TIMESTAMP))

    I tested this formula with 150 years back and still gets the correct
    results:

    SELECT DATEPART(wk, DATEADD(week, ROUND(52.1775 * -150, 0),
    CURRENT_TIMESTAMP))
    SELECT DATEPART(dw, DATEADD(week, ROUND(52.1775 * -150, 0),
    CURRENT_TIMESTAMP))

    HTH,

    Plamen Ratchev
    http://www.SQLStudio.com


+ Reply to Thread
Page 1 of 2 1 2 LastLast