Hi,
I am trying to select records based on date field between 01/2004 - 12/2004 in oracle. I tried using the following UserDefined SQL but its not working.
SELECT * FROM ACCOUNTS WHERE TO_CHAR(EFF_START_DT,'MM/DD/YYYY') >= '01/01/2004'
AND TO_CHAR(EFF_START_DT,'MM/DD/YYYY') <= '12/31/2004';
Thanks in Advance.
Selecting Records in between Two Dates.
Moderators: chulett, rschirm, roy
What's not working? Are you getting more or less rows than expected? What's your issue? You do realize that a lexicographic comparison works left to right, so that your format of MM/DD/YYYY doesn't work? Try using YYYY-MM-DD and you might be surprised. Better yet, use to_date on your "01/01/2004" instead of the data and maybe you can use the indexes that may exist on the columns in the table.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Re: Selecting Records in between Two Dates.
You would be better off using this:
SELECT * FROM ACCOUNTS WHERE EFF_START_DT between TO_DATE('01/01/2004','MM/DD/YYYY') and TO_DATE('12/31/2004','MM/DD/YYYY');
Always try to avoid wrapping the column in your table with a function like to_char. If you have an index on the eff-start_dt column, the optimizer will not be able to utilize it with the to_char function wrapped around it. This is true of most databases not just oracle. Also comparing chars may not bring the same results when comparing as when comparing dates.
SELECT * FROM ACCOUNTS WHERE EFF_START_DT between TO_DATE('01/01/2004','MM/DD/YYYY') and TO_DATE('12/31/2004','MM/DD/YYYY');
Always try to avoid wrapping the column in your table with a function like to_char. If you have an index on the eff-start_dt column, the optimizer will not be able to utilize it with the to_char function wrapped around it. This is true of most databases not just oracle. Also comparing chars may not bring the same results when comparing as when comparing dates.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com