First day and last day of a week

  • kamel Gazzah

    SSC Rookie

    Points: 43

    Comments posted to this topic are about the item First day and last day of a week

  • Phil Factor

    SSCoach

    Points: 19959

    If you haven't done so, it would be worth checking whether this will work with all the different language and DATEFORMAT settings. (actually I suspect it will work but that initial date assignment line might be tricky for 'YMD' formats as it doesn't specify the date format for the conversion of string to date). I'd use..

    set @fd=convert(DATETIME,'01/01/'+convert(varchar(4),@y),101)

    --which specifies the US interpretation of the date.

    Alternatively, you can get the same result without the procedure, and avoiding conversion issues, this way (I've done it so you can test against yours)...

    [font="Courier New"]DECLARE @year INT =2015, @week INT =25

    EXECUTE #usp_first_day_and_last_day_of_week @year,@week

    SELECT DATEADD (DAY,-DATEPART(dw,start)+1, start) AS StartOfWeek,

          DATEADD (DAY,7-DATEPART(dw,start), start) AS EndOfWeek

          FROM (VALUES (DATEADD(WEEK,@week-1,(datefromparts(@year,1,1))))) f(Start)

    [/font]

    Best wishes,
    Phil Factor
    Simple Talk

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • ScottPletcher

    SSC Guru

    Points: 98315

    Yet another version, as an in-line table-valued function, for easy use within a query:

    CREATE FUNCTION dbo.fn_first_day_and_last_day_of_week

    (

    @year smallint,

    @week tinyint

    )

    RETURNS TABLE

    AS

    RETURN (

    SELECT @year AS year, @week AS week,

    DATEADD(DAY, 7 * (@week - 1), first_day_of_week1) AS [first day],

    DATEADD(DAY, 7 * (@week - 1) + 6, first_day_of_week1) AS [last day]

    FROM (

    SELECT DATEADD(YEAR, @year - 1900, 0) AS jan_01

    ) AS assign_alias_names1

    CROSS APPLY (

    --back up to the day corresponding to @@DATEFIRST

    SELECT DATEADD(DAY, -DATEDIFF(DAY, CAST(0 + @@DATEFIRST - 1 AS datetime), jan_01) % 7, jan_01) AS first_day_of_week1

    ) AS assign_alias_names2

    )

    GO --end of function

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply