CODEWITHSUNDEEP
sqldatabasesql-scripts
Scott Bybee
Scott Bybee

Reputation: 1

Having problems with txn_date Between over a 24hour period broken up into two 12 hour chunks

I currently have a data pull that tracks data from 0730-1930 and 1930-0730. Data refreshes at the start of each time period. My problem is my script stops pulling a half hour early; it ends at 0700 and 1900 instead of 0730 and 1930. Copy below:

z0.txn_date Between (CASE WHEN To_Number(To_Char(SYSDATE,'hh24')) 
Between 7 AND 7+12-1 Then TRUNC(SYSDATE) + 7/24 WHEN 
To_Number(To_Char(SYSDATE,'hh24')) Between 7+12 AND 23 Then 
TRUNC(SYSDATE) + (7+12)/24 ELSE TRUNC(SYSDATE) - 1+ (7+12)/24 END) 
+ (0.5/24) AND (CASE WHEN To_Number(To_Char(SYSDATE,'hh24')) 
Between 7 AND 7+12-1 Then TRUNC(SYSDATE) + (7+12)/24 WHEN 
To_Number(To_Char(SYSDATE,'hh24')) Between 7+12 AND 23 Then 
TRUNC(SYSDATE) + 1 + 7/24 ELSE TRUNC(SYSDATE) + 1 + 7/24 END) + 
(0.5/24)

I have tried various forms of adding .5 to different sections, but this always breaks the script.

Upvotes: 0

Views: 47

Answers (0)

Related Questions