526互联

SQL日期操作函数（CONCAT、DATE_FORMAT、LAST_DAY）

发布时间 2023-07-22 15:25:04作者: 靠谱杨

获取某月底日期：SELECT LAST_DAY('2021-07-01') AS month_end_date;

拼接年月格式：

CONCAT(DATE_FORMAT(hp.planned_payment_date, '%Y-%m'), '-01')

如果数据库内存的是2023-07-19

经过处理后会变成：2023-07-01

SELECT bp.UNIT_ID                                                   AS UNIT_ID,
       hu.unit_name                                                 AS project_id_dept,
       sum(hp.planned_payment_amount)                               AS planned_payment_amount_sum,
       CONCAT(DATE_FORMAT(hp.planned_payment_date, '%Y-%m'), '-01') AS planned_payment_date,
       hp.pay_in_or_out
FROM byjz_fin_all.hlfn_project_position hp
         LEFT JOIN byjz_bus.hlpj_project bp ON hp.PROJECT_ID = bp.PROJECT_ID
         LEFT JOIN hzero_platform.hpfm_unit hu ON bp.UNIT_ID = hu.UNIT_ID
WHERE hp.forecast_status = 'FORECAST_CONFIRMED'
GROUP BY bp.UNIT_ID,
         CONCAT(DATE_FORMAT(hp.planned_payment_date, '%Y-%m'), '-01'), hp.pay_in_or_out
HAVING 1 = 1
ORDER BY unit_id DESC
LIMIT 10;