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)


    Best wishes,
    Phil Factor

  • 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




    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


    --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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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