时间戳

首页 > 解决方案 > SQL 拆分 MM 和 YY 以及 Concat 与字符串

sql - SQL 拆分 MM 和 YY 以及 Concat 与字符串

问题描述

提前感谢您的帮助！

我有一个日期字段 (A.event_dt)，我用它来创建新列，如下所示（一直到 12 月）。

to_char(A.event_dt, 'YYYYMM') as Event_Month,

case when ROW_NUMBER() OVER (PARTITION BY B.emp_nbr, E.cntry_enti_nbr, Event_Month order by A.event_dt asc) = 1  then 1 else 0 end as Unique_Event,


count(distinct concat(B.emp_nbr, E.cntry_enti_nbr, A.event_dt)),

case when CAST(A.event_dt as DATE FORMAT 'MM')(char(2)) = '06' then 1 else '' end as Jun,
case when CAST(A.event_dt as DATE FORMAT 'MM')(char(2)) = '07' then 1 else '' end as Jul,
case when CAST(A.event_dt as DATE FORMAT 'MM')(char(2)) = '08' then 1 else '' end as Aug,
case when CAST(A.event_dt as DATE FORMAT 'MM')(char(2)) = '09' then 1 else '' end as Sep,
case when CAST(A.event_dt as DATE FORMAT 'MM')(char(2)) = '10' then 1 else '' end as Oct,

我也想添加YY。

样本数据、电流输出和所需数据输出：

....EVENT_DT.....CURRENT OUTPUT......DESIRED DATA OUTPUT
....1/31/2020..........Jan................Jan20
....2/35/2019..........Feb................Feb19
....3/15/2020..........Mar................Mar20

所需的输出格式：

....EVENT_DT.....Feb19......Jan20..........Mar20
....2/25/2019........1..........................
....1/31/2020..................1................
....3/15/2020..............................1

编辑：我已经尝试了下面的代码但是我得到一个错误：（在'concat'关键字和'（'。）之间应该有'，'：

case when CAST(A.event_dt as DATE FORMAT 'MM')(char(2)) = '06' then 1 else '' end as concat('Jun',CAST(A.event_dt as DATE FORMAT 'YY')(char(2)))

编辑：我也尝试过这段代码，但是出现错误：“'AS'关键字和'（'之间的名称或unicode分隔标识符之类的东西。

case when extract(month from A.ramp_dt) = '12' then 1 else '' end as (to_char(A.ramp_dt, 'MonYYYY')),
case when extract(month from  A.ramp_dt) = '01' then 1 else '' end as (to_char(A.ramp_dt, 'MonYYYY')),
case when extract(month from  A.ramp_dt) = '02' then 1 else '' end as (to_char(A.ramp_dt, 'MonYYYY'))

标签： sqldateteradataconcat

解决方案

Perhaps you should just use to_char()?

to_char(a.event_dt, 'MMMMYY')

推荐阅读