参考答案: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