Home Forums SQL Server 2008 T-SQL (SS2K8) Calculate weekend RE: Calculate weekend

  • December 3, 2012 at 5:49 am

    #1564822

    Jason-299789 (12/3/2012)

    Using a Tally Table would improve this code significantly, also you have to be careful about using the fucntion datepart(dw,a_date) as in some cases Sunday isnt always represented by 1, and will depend on your regional settings.

    Eg : In the US, DW 1 is Sunday, yet in europe it can be DW 1 is monday.

    See books on line http://msdn.microsoft.com/en-us/library/ms174420.aspx

    To mitigate this you need to use the SET DATEFIRST to make it consistent across all regional settings, see http://msdn.microsoft.com/en-us/library/ms181598.aspx

    Jason,

    I know it sounds strange to those of us that use a Tally Table on a regular basis, but there are still a lot of folks that don't know what it is. You either have to explain, provide a link, or provide the full code (preferably some combination of those) for people that don't understand.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)