forcing a time stamp

Oracle DB - I am trying to create a date object based on another date. The new object should be 1 day prior, easy enough right (date -1); however, I am trying to force the time portion to be 10:00:00 PM (this is the time of a nightly extract and will be used for further date/time calculations). I have tried using the to_date function with the following date formats to no avail

to_date((date-1),‘DD-MM-YYYY HH24:MI:SS’)
to_date((date-1),‘DD-MM-YYYY HH:MI:SS’)
to_date((trunc(date)-1),‘DD-MM-YYYY HH24:MI:SS’)

All will return the correct date with 12:00:00 AM as the default time and I have tried plugging in or hard coding 22:00:00 and 10:00:00 PM, the object will parse OK but when used in a query a get a connection/SQL error that the date format is not recognized. I know I must be overlooking something obvious here.


jswoboda :us: (BOB member since 2002-06-20)

Try

trunc(whatever_date_expression) + (10/24)

to get a 10 o’clock time stamp. Works great. 8)


Dave Rathbun :us: (BOB member since 2002-06-06)

Just add the time … literally

to_date(blah blah) + (22/24)

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Bingo. Thanks guys, this is the exact syntax I went with -

to_date((trunc((DWGEN.DW_UTILITY_ROP.UROP_CREATED_TIME_STAMP) -1)+22/24),‘DD-MM-YYYY HH24:MI:SS’)


jswoboda :us: (BOB member since 2002-06-20)