英文:
Snowflake SQL, converting number YYYYWW into date
问题
在Snowflake中,我有一个数值列,其值类似于202201、202305、202248,表示年周的组合。如何将其转换为该周的第一天或最后一天?
例如,202251将变成'2022-12-19'或'2022-12-25'(因为该周从星期一开始)。
感谢帮助
我尝试过以下代码:
select distinct week_id
,to_date(concat
(
substr(week_id,1,4)
,substr(week_id,5,2)
)
,'YYYYWW'
) as Date_value
from MyTable
但我只收到错误消息,表示无法将'202249'解析为'YYYYWW'日期格式。
英文:
in snowflake i have numeric column with values like 202201, 202305,202248 that refers to year week combination. How can i convert it into first or last day of tha week?
for example 202251 will be '2022-12-19' or '2022-12-25' (as the week starts on Monday)
thx for help
i have tried
select distinct week_id
,to_date(concat
(
substr(week_id,1,4)
,substr(week_id,5,2)
)
,'YYYYWW'
) as Date_value
from MyTable
but i got only error msg as Can't Parse '202249' as date with format 'YYYYWW'
答案1
得分: 1
以下是您要翻译的内容:
如果我们切换到TRY_TO_DATE格式,它将不会出错,这可以使调试更简单。
然后当我们尝试 'YYYYWW' 时,我们看到它失败了:
select week_id
,try_to_date(week_id, 'YYYYWW') as Date_value
from values
('202201'),
('202305'),
('202248'),
('202249')
t(week_id);
WEEK_ID | DATE_VALUE |
---|---|
202201 | null |
202305 | null |
202248 | null |
202249 | null |
切换为仅使用年份的子串和周数的数字:
select week_id
,try_to_date(left(week_id,4), 'YYYY') as just_year
,try_to_number(substr(week_id,5,2)) as week_num
from values
('202201'),
('202305'),
('202248'),
('202249')
t(week_id);
现在我们得到了可以工作的部分。
WEEK_ID | JUST_YEAR | WEEK_NUM |
---|---|---|
202201 | 2022-01-01 | 1 |
202305 | 2023-01-01 | 5 |
202248 | 2022-01-01 | 48 |
202249 | 2022-01-01 | 49 |
select week_id
,try_to_date(left(week_id,4), 'YYYY') as just_year
,try_to_number(substr(week_id,5,2)) as week_num
,dateadd(week, week_num, just_year) as answer
from values
('202201'),
('202305'),
('202248'),
('202249')
t(week_id);
WEEK_ID | JUST_YEAR | WEEK_NUM | ANSWER |
---|---|---|---|
202201 | 2022-01-01 | 1 | 2022-01-08 |
202305 | 2023-01-01 | 5 | 2023-02-05 |
202248 | 2022-01-01 | 48 | 2022-12-03 |
202249 | 2022-01-01 | 49 | 2022-12-10 |
这可以合并为一个查询,如下所示:
select week_id
,dateadd(week, try_to_number(substr(week_id,5,2)), try_to_date(left(week_id,4), 'YYYY')) as answer
from values
('202201'),
('202305'),
('202248'),
('202249')
t(week_id);
英文:
If we swap to TRY_TO_DATE form, it will not explode, which can make for simpler debugging.
And then when we try 'YYYYWW' we see if fails:
select week_id
,try_to_date(week_id, 'YYYYWW') as Date_value
from values
('202201'),
('202305'),
('202248'),
('202249')
t(week_id);
WEEK_ID | DATE_VALUE |
---|---|
202201 | null |
202305 | null |
202248 | null |
202249 | null |
swapping to a substring for just the year, and a number for the week:
select week_id
,try_to_date(left(week_id,4), 'YYYY') as just_year
,try_to_number(substr(week_id,5,2)) as week_num
from values
('202201'),
('202305'),
('202248'),
('202249')
t(week_id);
now we get those parts as something workable.
WEEK_ID | JUST_YEAR | WEEK_NUM |
---|---|---|
202201 | 2022-01-01 | 1 |
202305 | 2023-01-01 | 5 |
202248 | 2022-01-01 | 48 |
202249 | 2022-01-01 | 49 |
Now we can use DATEADD and the WEEK like so:
select week_id
,try_to_date(left(week_id,4), 'YYYY') as just_year
,try_to_number(substr(week_id,5,2)) as week_num
,dateadd(week, week_num, just_year) as answer
from values
('202201'),
('202305'),
('202248'),
('202249')
t(week_id);
WEEK_ID | JUST_YEAR | WEEK_NUM | ANSWER |
---|---|---|---|
202201 | 2022-01-01 | 1 | 2022-01-08 |
202305 | 2023-01-01 | 5 | 2023-02-05 |
202248 | 2022-01-01 | 48 | 2022-12-03 |
202249 | 2022-01-01 | 49 | 2022-12-10 |
which can all be merge into one like:
select week_id
,dateadd(week, try_to_number(substr(week_id,5,2)), try_to_date(left(week_id,4), 'YYYY')) as answer
from values
('202201'),
('202305'),
('202248'),
('202249')
t(week_id);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论