Oracle View

5 pts.
Tags:
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following