Snowflake SQL,将数字YYYYWW转换为日期

huangapple go评论86阅读模式
英文:

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

现在我们可以使用DATEADDWEEK来实现:

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);

huangapple
  • 本文由 发表于 2023年2月6日 09:24:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/75356628.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定