ISO8601将周日期转换为日历日期 [英] ISO8601 Convert Week Date to Calendar Date
问题描述
我正在尝试以ISO 8601格式确定给定的给定一周的日期(即YYYY-W ##)。我的最终目标是将ISO 8601周日期转换为ISO 8601日历日期。我需要这样做在TSQL(我正在使用SQL Server 2005),我不知道是否有内置的SQL Server 05允许这一点,但它有助于看到另一种语言的例子或在通用伪码
I'm trying to determine the day of a given a week date given (i.e. YYYY-W##) in the ISO 8601 format. My end goal is to convert an ISO 8601 Week Date to an ISO 8601 Calendar Date. I need to do this in TSQL (I'm working with SQL Server 2005), I'm not sure if there's anything already built-in to SQL Server 05 that allows this, but it would help to see an example in another language or in generic pseudocode
更新:
对不起,如果我的问题的结构令人困惑。基本上,我有一个ISO8601周日期,我试图转换为ISO8601日历日期。
Sorry if the structure of my question is confusing. Basically, I have a ISO8601 week date that I'm trying to convert to ISO8601 Calendar Date.
示例(从维基百科)
ISO 8601周日期:2012-W02(YYYY-W ##)
ISO 8601 Week Date: 2012-W02 (YYYY-W##)
转换为...
ISO 8601日历日期:2012-01-09(YYYY-MM-DD )
ISO 8601 Calendar Date: 2012-01-09 (YYYY-MM-DD)
由于在我的周日例子中没有给出日间组件,所以可以假设一周中的第一天。
Since the day component isn't given in my week date example, the first day of the week can be assumed.
推荐答案
尝试以下解决方案:
declare
@date varchar(10)
,@convertedDate datetime
,@wk int
,@yr int
set @date = '2012-W02';
set @yr = parsename(replace(@date, '-W', '.'), 2)
set @wk = parsename(replace(@date, '-W', '.'), 1)
set @convertedDate = convert(varchar(10), dateadd(week, @wk, dateadd (year, @yr-1900, 0)) - 5 - datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0))), 121)
select
'Year' = @yr
,'Week' = @wk
,'Date' = @convertedDate
输出:
-----------------------------------------
| Year | Week | Date |
-----------------------------------------
| 2012 | 2 | 2012-01-09 00:00:00.000 |
-----------------------------------------
这篇关于ISO8601将周日期转换为日历日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!