asked on

Need help with SQL

I need to write a sql to fetch the data which has been created between yesterday 5pm and today 11 am .I am using the following criteria to achieve that and i am getting an error message saying not a valid month.

We are on oracle database.

AND A.DTTM_STAMP_SEC >= TO_DATE(SYSDATE -1|| '17','MM/DD/YYYY HH24')
AND A.DTTM_STAMP_SEC < TO_DATE(SYSDATE || '16','MM/DD/YYYY HH24')

Thanks
Chaitu235
Any help on this is appreciated.

ASKER CERTIFIED SOLUTION

flow01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

flow01

OR
AND A.DTTM_STAMP_SEC >= TO_DATE(TO_CHAR(SYSDATE -1,MM/DD/YYYY ') || '17','MM/DD/YYYY HH24')
AND A.DTTM_STAMP_SEC < TO_DATE(TO_CHAR(SYSDATE,MM/DD/YYYY ')|| '16','MM/DD/YYYY HH24')

WHERE THE TO_CHAR WITH MM/DD/YYYY FORMAT RETURNS THE DAY (FORMATTED) AT 00:00:00

Thommy

@flow01:
In your 2nd comment the leading apostrophs for datetime format strings are missing:

AND A.DTTM_STAMP_SEC >= TO_DATE(TO_CHAR(SYSDATE -1,'MM/DD/YYYY ') || '17','MM/DD/YYYY HH24')
AND A.DTTM_STAMP_SEC < TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY ')|| '16','MM/DD/YYYY HH24')

Helena Marková

I have made some modification :)
select trunc(SYSDATE) - (7/24) A,TO_DATE(TO_CHAR(SYSDATE -1,'MM/DD/YYYY ') || '17','MM/DD/YYYY HH24') B,
trunc(SYSDATE) + (11/24) C,TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY ')|| '11','MM/DD/YYYY HH24') D
from dual;

Steve Wales

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: flow01 (https:#a41070464)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

sjwales
Experts-Exchange Cleanup Volunteer