Question

Oracle View

Oracle Views

How can I get my view to return SYSDATE-4 hours? I currently have WHERE event.cdts = TO_CHAR (SYSDATE, 'yyyymmdd')is creation of the view. It is not returning anything.

Your where clause should read something like this:

WHERE event.cdts >= TO_CHAR ((SYSDATE-4/24), 'yyyymmdd')

Another issue is the data type of event.cdts. It should be a date field. This to_char statement strips off all the time elements of sysdate so it will show all records for the day that sysdate -4/24 equates to. If event.cdts is a varchar2 field it must contain a time portion as well for this to work. You really want the where clause to look like this:

WHERE event.cdts >= sysdate - (4/24)

if events.cdts is a varchar2, you need to include the time component in your TO_CHAR:

WHERE event.cdts = TO_CHAR ((SYSDATE-4/24), 'yyyymmdd 24HH:MI:SS')

Hope this helps.