Convert to date (ISO year and week number)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Convert to date (ISO year and week number)

Author

Topic

caijunling
Starting Member

5 Posts

Posted - 2010-01-24 : 22:29:09

This function is helpful!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515

I have a question regarding converting from year (integer) and week number(integer) to the first day ( datetime) of given ISO year nad week nr
For example I have year (2009) and week number (53) and I need to return '2009-12-28' (the first day of the weeknr-- 200953)
Can anyone help ?
Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-25 : 02:44:04

How does week number of '2009-12-18' is 53?

Madhivanan

Failing to plan is Planning to fail

caijunling
Starting Member

5 Posts

Posted - 2010-01-25 : 03:17:00

Sorry!,I have corrected.
I need to return '2009-12-28' (not '2009-12-18' )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-25 : 09:19:26

One of the methods is

declare @year int, @weekno int
select @year=2009, @weekno=53
select min(dates) from
(
select dateadd(day,number,dateadd(year,@year-1900,0)) as dates from master..spt_values
where type='p'
) as t
where datepart(week,dates)=@weekno

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 09:53:57

wont this be enough?

select dateadd(week,@weekno-1,dateadd(year,@year-1900,0)) as dates

caijunling
Starting Member

5 Posts

Posted - 2010-01-25 : 10:03:09

quote:
Originally posted by madhivanan

One of the methods is

declare @year int, @weekno int
select @year=2009, @weekno=53
select min(dates) from
(
select dateadd(day,number,dateadd(year,@year-1900,0)) as dates from master..spt_values
where type='p'
) as t
where datepart(week,dates)=@weekno

Madhivanan

Failing to plan is Planning to fail

Thanks!
But the week number and year is not ISO year formate.
In ISO calendar '2010-01-04' is 2010-kw01,how can I convert from 201001 (year and week number) to '2010-01-04'( first day of ISO calendar)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 10:17:51

[code]select dateadd(week,right(@yearwk,2),dateadd(year,left(@yearwk,4)-1900,0)) as dates[/code]

caijunling
Starting Member

5 Posts

Posted - 2010-01-25 : 18:58:23

quote:

Originally posted by visakh16

select dateadd(week,right(@yearwk,2),dateadd(year,left(@yearwk,4)-1900,0)) as dates

this way does not work for ISO calendar too.

select dateadd(week,convert(int,right('200953',2)),dateadd(year,convert(int,left('200953',4))-1900,0))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 00:55:06

I think this is what you want

CREATE FUNCTION dbo.fnISOMonday
(
    @theYear SMALLINT,
    @theWeek TINYINT
)
RETURNS DATETIME
AS
BEGIN
    RETURN  (
	        SELECT  DATEADD(DAY, 7 * @theWeek - 7, CurrentYear)
                FROM    (
                            SELECT  DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,
                                    DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear
                            FROM    (
                                        SELECT  DATEADD(YEAR, @theYear - 1900, 3) AS Jan4
                                        WHERE   @theYear BETWEEN 1900 AND 9999
						AND @theWeek BETWEEN 1 AND 53
                                    ) AS x
                        ) AS d
                WHERE   DATEADD(DAY, 7 * @theWeek - 7, CurrentYear) < NextYear
            )
END

N 56°04'39.26"
E 12°55'05.63"

caijunling
Starting Member

5 Posts

Posted - 2010-01-26 : 02:29:14

I have tested and it is correct.
Thanks for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 04:58:54

Or you can use the function posted here
http://weblogs.sqlteam.com/peterl/archive/2009/12/01/How-to-get-a-date-from-Year-week-and-weekday.aspx

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources