DSXchange

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.

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.

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.