July 18, 2005 at 3:13 am
Sombody now how to calculate first monday day from given week
For example I need get the day from a given week:
ISO date ‘20050101’ is the week 53 in ISO 8601
If a have the week number 53 how I can calculate the ISO date?
Thank for all in advance.
July 20, 2005 at 10:59 am
Try this one:
DECLARE @Date datetime
SET @Date = getdate()
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date)) AS 'Last day of the month'
SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS 'First day of the month'
Hope this helps. Good luck
July 4, 2011 at 2:12 am
Great answer!
FAIL
July 6, 2011 at 1:33 pm
I'm pretty sure this question is born out of the fact that T-SQL does not define the "first week of the year" in the same way as the ISO 8601 standard. In T-SQL any week with January 1st is considered week one. In the ISO standard, week one is the week with the first Thursday of the year. This discrepancy causes no little difficulty in calculating ISO week values in T-SQL.
The solution I propose below tackles the problem in three steps: 1) find the first Thursday of the year; 2) add the needed number of weeks to that date to arrive at the target ISO week; 3) subtract three days from the target week Thursday to arrive at the target week Monday. The code below captures two variable values: the first day of the year (needed to know which year is in view) and the target ISO week of the year.
declare @FirstDayOfTheYear as datetime, @TargetISOWeek as tinyint
set @FirstDayOfTheYear = '20110101'
set @TargetISOWeek = 20
select
/* Calculate the First Thursday of the Year */
FirstThursday = case when datepart(dw, @FirstDayOfTheYear) <= 5 then
dateadd(d, 5 - datepart(dw, @FirstDayOfTheYear), @FirstDayOfTheYear) else
dateadd(d, 12 - datepart(dw, @FirstDayOfTheYear), @FirstDayOfTheYear) end,
/* Add the proper number of weeks, which is the @TargetWeek value minus one. */
TargetWeekThursday = dateadd(week, @TargetISOWeek - 1,
case when datepart(dw, @FirstDayOfTheYear) <= 5 then
dateadd(d, 5 - datepart(dw, @FirstDayOfTheYear), @FirstDayOfTheYear) else
dateadd(d, 12 - datepart(dw, @FirstDayOfTheYear), @FirstDayOfTheYear) end),
/* Substract three days to get to the Monday of the target week. */
TargetWeekMonday = dateadd(day, -3, dateadd(week, @TargetISOWeek - 1,
case when datepart(dw, @FirstDayOfTheYear) <= 5 then
dateadd(d, 5 - datepart(dw, @FirstDayOfTheYear), @FirstDayOfTheYear) else
dateadd(d, 12 - datepart(dw, @FirstDayOfTheYear), @FirstDayOfTheYear) end))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply