need help with measuring dates

360 pts.
Tags:
Oracle
SQL
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?

Answer Wiki

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

Discuss This Question: 7  Replies

 
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
  • BigKat
    try using: between current_date and current_date - 20 days
    8,350 pointsBadges:
    report
  • BigKat
    that is createdt between...
    8,350 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • carlosdl
    Why do you need to convert dates to char ?Also, you are subtracting 20 days, not years (date arithmetics work on days)
    70,190 pointsBadges:
    report
  • carlosdl
    "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...
    70,190 pointsBadges:
    report
  • carlosdl
    Also, change the order of your dates to define the range.  Instead of 'between sysdate and sysdate-20' use 'between sysdate-20 and sysdate'.
    70,190 pointsBadges:
    report
  • YOUSAFALIBS
    createdt > (sysdate-20)
    80 pointsBadges:
    report

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