SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Convert to date (ISO year and week number)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

caijunling
Starting Member

5 Posts

Posted - 01/24/2010 :  22:29:09  Show Profile  Reply with Quote
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!

Edited by - caijunling on 01/25/2010 03:15:35

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 01/25/2010 :  02:44:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
How does week number of '2009-12-18' is 53?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

caijunling
Starting Member

5 Posts

Posted - 01/25/2010 :  03:17:00  Show Profile  Reply with Quote
Sorry!,I have corrected.
I need to return '2009-12-28' (not '2009-12-18' )
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 01/25/2010 :  09:19:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/25/2010 :  09:53:57  Show Profile  Reply with Quote
wont this be enough?

select dateadd(week,@weekno-1,dateadd(year,@year-1900,0)) as dates
Go to Top of Page

caijunling
Starting Member

5 Posts

Posted - 01/25/2010 :  10:03:09  Show Profile  Reply with Quote
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)?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/25/2010 :  10:17:51  Show Profile  Reply with Quote
select dateadd(week,right(@yearwk,2),dateadd(year,left(@yearwk,4)-1900,0)) as dates
Go to Top of Page

caijunling
Starting Member

5 Posts

Posted - 01/25/2010 :  18:58:23  Show Profile  Reply with Quote
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))
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 01/26/2010 :  00:55:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

caijunling
Starting Member

5 Posts

Posted - 01/26/2010 :  02:29:14  Show Profile  Reply with Quote
I have tested and it is correct.
Thanks for your help!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 01/26/2010 :  04:58:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000