Return Records Changed Since Yesterday Morning 1

[AngelB]

Hi All,

I need to modify a script which used to return everything changed since 'sysdate - 1'. This now needs to be updated to be more specific - everything changed between 6:00am yesterday and 6:00am today. I wouldn't have trouble if it was a specific date, but since it needs to look at 'yesterday' whenever the script is run, sysdate has to be used and I'm having trouble with the formatting. What I need to do is change the line from

DATE_CHANGED > SYSDATE - 1

to be something like

DATE_CHANGED >= TO_CHAR(SYSDATE - 1 06:00am, 'DD-MON-YY HH:MI:SS')

but I'm not sure of the exact formatting.

Thanks in advance


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[taupirho]

Try this:-

where date_changed between to_date(trunc(sysdate - 1) || ' 06:00','dd-mon-yy hh24:mi')
and to_date(trunc(sysdate) || ' 06:00','dd-mon-yy hh24:mi')

 

[AngelB]

Wonderful, thanks very much for your help!


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[Beilstwh]

Why even bother with a to_date conversion. simply do

where date_changed between trunc(sysdate - 1) + 6/24
and trunc(sysdate) + 6/24

Bill
Oracle DBA/Developer
New York State, USA