BETWEEN DATETIME STATEMENT NOT WORKING
-
I am using the follow segment in my query, but it is only running the query by date :-
(to_date(PROCESS_DATE, 'dd/mm/yyyy hh24:mi:ss'))
BETWEEN to_date(START_DATE, 'dd/mm/yyyy hh24:mi:ss') and
to_date(END_DATE, 'dd/mm/yyyy hh24:mi:ss')
I have results that are on the same day but different times, so my query must return by date and time, but I am getting multiple results indicating it is only querying the date element and not the time. Any help pppllleeeaaassseee
-
try HH24 (upper case)
and are those 3 columns really stored as character values instead of dates? there is no other reason to convert to date to compare.
-
Where is your question? Are you trying to determine if ProcessDate is between StartDate and EndDate?
-
Is this Oracle? If so I believe to_date casts a column to a date. The format code is to help with the conversion, not to add time, which might explain why the comparison is made based on date not date and time.
What data types are the source columns? If they are strings with EU datetime formats minus seconds then you might be able to use to_timestamp. If the seconds are present, but truncated in the screen print, then add :SS where necessary.
SELECT TO_TIMESTAMP('23/05/2020 14:15', 'DD/MM/YYYY HH24:MI') TS,
TO_CHAR(TO_TIMESTAMP('23/05/2020 14:15', 'DD/MM/YYYY HH24:MI'), 'DD/MM/YYYY HH24:MI:SS') TC
FROM dual
SELECT PROCESS_DATE, START_DATE, END_DATE
FROM table p
WHERE TO_TIMESTAMP(p.PROCESS_DATE, 'DD/MM/YYYY HH24:MI') >= TO_TIMESTAMP(p.START_DATE, 'DD/MM/YYYY HH24:MI')
AND TO_TIMESTAMP(p.PROCESS_DATE, 'DD/MM/YYYY HH24:MI') <= TO_TIMESTAMP(p.END_DATE, 'DD/MM/YYYY HH24:MI')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply