| Author |
 Topic  |
|
|
caijunling
Starting Member
5 Posts |
 Posted - 01/24/2010 : 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! |
Edited by - caijunling on 01/25/2010 03:15:35
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 01/25/2010 : 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 - 01/25/2010 : 03:17:00
|
Sorry!,I have corrected.
I need to return '2009-12-28' (not '2009-12-18' )
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 01/25/2010 : 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
India
47173 Posts |
Posted - 01/25/2010 : 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 - 01/25/2010 : 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
India
47173 Posts |
Posted - 01/25/2010 : 10:17:51
|
select dateadd(week,right(@yearwk,2),dateadd(year,left(@yearwk,4)-1900,0)) as dates |
|
|
|
caijunling
Starting Member
5 Posts |
Posted - 01/25/2010 : 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
Sweden
29138 Posts |
Posted - 01/26/2010 : 00:55:06
|
I think this is what you wantCREATE 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 - 01/26/2010 : 02:29:14
|
I have tested and it is correct.
Thanks for your help!
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
| |
Topic |
|
Convert to date (ISO year and week number)
