SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using SYSDATE in a Timestamp Date Range

OscarBoots1Nov 22 2017 — edited Dec 2 2017

Hi Helpful Forum Members,

I've got some code that gives a Date Range from Midnite the day before yesterday to Midnite yesterday.

Where timestamp_field between to_timestamp('19-11-2017 13:00:00', 'DD-MM-YYYY HH-MM-SS.ff') + interval hour + 11 and to_timestamp('20-11-2017 12:59:59', 'DD-MM-YYYY HH-MM-SS.ff') + interval hour + 11

I want to automate this so it always give the Data for the same period, The day before yesterday at Midnite to Midnite yesterday.

I've tried replacing the Date part '19-11-2017' with sysdate so I can use sysdate-1 for yesterday & sysdate-2 for the day before yesterday.

Where timestamp_field between to_timestamp(sysdate-2 '13:00:00', 'DD-MM-YYYY HH-MM-SS.ff') + interval hour + 11 and to_timestamp(sysdate-1 '12:59:59', 'DD-MM-YYYY HH-MM-SS.ff') + interval hour + 11

Can anyone help me here with the correct syntax?

Thanks

This post has been answered by mathguy on Nov 22 2017

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 30 2017

Added on Nov 22 2017

10 comments

1,236 views