目录
一、odps 取年月日
--取年
SELECT TO_CHAR(DATE'2023-01-02','YYYY');
--取月
SELECT TO_CHAR(DATE'2023-01-02','MM');
--取日
SELECT TO_CHAR(DATE'2023-01-02','DD');
二、odps 取当年初、当月初、当日
--当年初
CAST(DATETRUNC(TO_DATE('20230102','YYYYMMDD'),'YYYY') AS DATE)
--当月初
CAST(DATETRUNC(TO_DATE('20230102','YYYYMMDD'),'MM') AS DATE)
--当天
CAST(TO_DATE('20230102','YYYYMMDD') AS DATE)
三、odps 上年末、上月末
--上年末
CAST(DATEADD(DATETRUNC(TO_DATE('20230102','YYYYMMDD'),'YYYY'),-1,'DD') AS DATE)
--上月末
CAST(LASTDAY(TO_DATE(ADD_MONTHS(TO_DATE('20230102','YYYYMMDD'),-1),'YYYY-MM-DD')) AS DATE)
四、odps 上年初、上月初
--上年初
CAST(DATETRUNC(CAST(DATETRUNC(TO_DATE('20230102','YYYYMMDD'),'YYYY') AS DATE)-1,'YYYY') AS DATE)
--上月初
CAST(DATETRUNC(TO_DATE(ADD_MONTHS(TO_DATE('20230102','YYYYMMDD'),-1),'YYYY-MM-DD'),'MM') AS DATE)