need help with measuring dates
I am trying to select records from a table where the CREATEDT field is between SYSDATE and 20 days prior.
select distinct(createdt)
from U_table
where to_char(createdt, 'dd/mm/yyyy') between to_char(sysdate, 'dd/mm/yyyy')
and to_char(sysdate - 20, 'dd/mm/yyyy')
There are records in that table that are in the date range but the SQL is not finding them. What am I doing wrong?
Software/Hardware used:
Software/Hardware used:
ASKED:
August 27, 2012 7:34 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
try using:
between current_date and current_date – 20 days
that is createdt between…
If CREATEDT is a date column, don’t use TO_CHAR() to convert it to a text string in ‘DD/MM/YYYY’ format. And don’t convert SYSDATE to a text string in ‘DD/MM/YYYY’ format. As soon as you convert to those formats, you mostly make it impossible to compare for a date range. Leave the values alone and just compare them directly to each other. — Tom
Why do you need to convert dates to char ?Also, you are subtracting 20 days, not years (date arithmetics work on days)
“Also, you are subtracting 20 days, not years (date arithmetics work on days)”
Just ignore this. Not sure why I thought you were trying to substract 20 years…
Also, change the order of your dates to define the range.
Instead of ‘between sysdate and sysdate-20′ use ‘between sysdate-20 and sysdate’.
createdt > (sysdate-20)