First day and last day of a week

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

  • 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

  • Thanks for the script.

  • 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