October 15, 2015 at 4:09 am
Comments posted to this topic are about the item First day and last day of a week
November 10, 2015 at 4:52 am
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
November 10, 2015 at 6:23 am
Thanks for the script.
November 10, 2015 at 9:31 am
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) "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