CODEWITHSUNDEEP
sqlsql-serversql-server-2008date
freand
freand

Reputation: 181

SQL Server : get date with inparameters year,week,weekday

I have searched and have yet to find this little helpful snippet.

I want to input...

Expected result: 2014-01-07 (seventh of January)

And get the full date in return, anyone?

EDIT: My server is SQL 2008

The finished code thanks to all!.

declare @year int = 2014
declare @weeknr int = 2
declare @daynroffset int = 2


SELECT 
  DATEADD(DAY,+ (@daynroffset-1),
    DATEADD(DAY,-DATEPART(DW,CAST('1/1/' + cast(@year as varchar) AS Date))+2,DATEADD(WK,@weeknr-    1,CAST('1/1/' + cast(@year as varchar) AS Date)))
  )

Upvotes: 3

Views: 1845

Answers (4)

TToni
TToni

Reputation: 9391

The other answers (so far) use SQL Server default mechanisms to determine week and day of week. In this case the current language setting determines the day of the week (through the @@DATEFIRST setting) and the DATEPART(wk uses Jan 1st as the fixed date contained in week 1.

To get a deterministic answer independent of the language setting one can use the ISO 8601 week standard which starts a week on Mondays and where the first week always contains Jan 4th.

This code determines the date based on ISO weeks:

declare @year int = 2016
declare @isoweek int = 22
declare @isoday int = 2

-- ISO-WEEK 1 always contains 4th Jan, so let's use this as a base
declare @date datetime = cast(cast(@year as varchar(4)) + '-01-04T12:00:00' as datetime)

-- Offset the wanted DayOfWeek versus our base date
-- We also set DATEFIRST temporarily because it affects DayOfWeek
-- ISO-Weeks always start on Monday
declare @datefirst int = @@DATEFIRST
SET DATEFIRST 1
declare @offset int = datepart(dw, @date) - 1
SET DATEFIRST @datefirst

-- Add given day and week to basedate
set @date = dateadd(day, @isoday - 1 - @offset, dateadd(wk, @isoweek - 1, @date))

print @date

Upvotes: 5

Brandon
Brandon

Reputation: 702

This may have issues near the year boundary, but it works for the example data given. You may want to add further validations. I've broken down each step of the datetime manipulation into a new field, so you can see it being constructed

2008

DECLARE @Year INT = 2014
DECLARE @WeekNum INT = 2
DECLARE @WeekDay INT = 2

SELECT
    BaseDate = CAST( @year AS VARCHAR(4) )
  , RoundToWeekStart = DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST( @year AS VARCHAR(4) )), 0) -- Will be a Monday
  , AddWeeksToRoundedDate = DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST( @year AS VARCHAR(4) )), 0) )
  , AddWeekDay = DATEADD( DAY, @WeekDay - 1, DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST( @year AS VARCHAR(4) )), 0) ) )

2012+

DECLARE @Year INT = 2014
DECLARE @WeekNum INT = 2
DECLARE @WeekDay INT = 2

SELECT
    BaseDate = DATEFROMPARTS(@Year, 1, 1)
  , RoundToWeekStart = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(@Year, 1, 1)), 0) -- Will be a Monday
  , AddWeeksToRoundedDate = DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(@Year, 1, 1)), 0) )
  , AddWeekDay = DATEADD( DAY, @WeekDay - 1, DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(@Year, 1, 1)), 0) ) )

Upvotes: 0

SmartDev
SmartDev

Reputation: 2862

declare @year int = 2014
declare @week int = 2
declare @day int = 2

declare @date datetime = cast(cast(@year as varchar(20)) + '-01-01' as datetime)
declare @offset int = datepart(dw, @date) - 1
set @date = dateadd(day, @day - @offset, dateadd(ww, @week - 1, @date))

print @date

Upvotes: 1

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

CODE:

2012+:
DATEADD(DAY,-DATEPART(DW,DATEFROMPARTS("YEAR",1,1))+1+"DAY OF WEEK",DATEADD(WK,"WEEK NUMBER"-1,DATEFROMPARTS(2014,1,1)))

2008+:
SELECT DATEADD(DAY,-DATEPART(DW,CAST(CONCAT('1/1/',"YEAR") AS Date))+1+"DAY OF WEEK",DATEADD(WK,"WEEK NUMBER"-1,CAST(CONCAT('1/1/',"YEAR") AS Date)))

Simply substitue the values where necessary.

This will work for any date.

Upvotes: 3

Related Questions