【入门留存】date_diff 应用实例--如何在使用T-1数据在BI看板中体现月至今数据

参考答案：SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(sysdate( - 1), 1, 10) AS DATE)) AS DATE), CAST(sysdate( 0) AS DATE))

当前时间 today='2023-08-01'
sysdate( - 1)=('2023-07-31')
sysdate( 0)=('2023-08-01')
T-1数据应当是7.1-7.31数据，天数应当为31

SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(sysdate( - 1), 1, 10) AS DATE)) AS DATE), CAST(sysdate( -1) AS DATE))
SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(('2023-07-31'), 1, 10) AS DATE)) AS DATE), CAST(('2023-07-31') AS DATE))
--30（错误）

SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(sysdate( - 1), 1, 10) AS DATE)) AS DATE), CAST(sysdate( 0) AS DATE))
SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(('2023-07-31'), 1, 10) AS DATE)) AS DATE), CAST(('2023-08-01') AS DATE))
--31

SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(sysdate( 0), 1, 10) AS DATE)) AS DATE), CAST(sysdate( 0) AS DATE))
SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(('2023-08-01'), 1, 10) AS DATE)) AS DATE), CAST(('2023-08-01') AS DATE))
--0（错误）

当前时间 today='2023-08-02'
sysdate( - 1)=('2023-08-01')
sysdate( 0)=('2023-08-02')
T-1数据应当是8.1-8.1数据，天数应当为1
SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(sysdate( - 1), 1, 10) AS DATE)) AS DATE), CAST(sysdate( -1) AS DATE))
SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(('2023-08-01'), 1, 10) AS DATE)) AS DATE), CAST(('2023-08-01') AS DATE))
--0（错误）

SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(sysdate( - 1), 1, 10) AS DATE)) AS DATE), CAST(sysdate( 0) AS DATE))
SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(('2023-08-01'), 1, 10) AS DATE)) AS DATE), CAST(('2023-08-02') AS DATE))
--1

SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(sysdate( 0), 1, 10) AS DATE)) AS DATE), CAST(sysdate( 0) AS DATE))
SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(('2023-08-02'), 1, 10) AS DATE)) AS DATE), CAST(('2023-08-02') AS DATE))
--1

当前时间today='2023-07-31'
sysdate( - 1)=('2023-07-30')
sysdate( 0)=('2023-07-31')
T-1数据应当是7.1-7.30数据，天数应当为30

SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(sysdate( - 1), 1, 10) AS DATE)) AS DATE), CAST(sysdate( -1) AS DATE))
SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(('2023-07-30'), 1, 10) AS DATE)) AS DATE), CAST(('2023-07-30') AS DATE))
--29（错误）

SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(sysdate( - 1), 1, 10) AS DATE)) AS DATE), CAST(sysdate( 0) AS DATE))
SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(('2023-07-30'), 1, 10) AS DATE)) AS DATE), CAST(('2023-07-31') AS DATE))
--30

SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(sysdate( 0), 1, 10) AS DATE)) AS DATE), CAST(sysdate( 0) AS DATE))
SELECT date_diff('day', CAST(date_trunc('month', CAST(SUBSTR(('2023-07-31'), 1, 10) AS DATE)) AS DATE), CAST(('2023-07-31') AS DATE))
--30