Compare Dates with Different formats

I need to compare dates with possibly different formats. My query is fairly large already. The main point is in red:

SELECT pwl.request_id, pwl.work_list_id, pwl.value
FROM PRODUCT_WORK_LIST pwl, SERVICE_REQUEST sr
WHERE sr.request_id = pwl.request_id
AND (sr.request_status <> 'PND' AND sr.request_status <> 'DEL'
AND sr.request_status <> 'PRG' AND sr.request_status <> 'SUB')
AND pwl.request_id = ANY (
SELECT PRODUCT_WORK_LIST.REQUEST_ID
FROM (CPOT_GROUP INNER JOIN GROUP_TO_PRODUCT ON CPOT_GROUP.ID = GROUP_TO_PRODUCT.GROUP_ID)
INNER JOIN PRODUCT_WORK_LIST ON GROUP_TO_PRODUCT.PRODUCT_ID = PRODUCT_WORK_LIST.PRODUCT_ID
WHERE description = 'Developer'
AND PRODUCT_WORK_LIST.value = to_date('2004-05-19', 'mm/dd/yyyy')
AND work_list_id = 5)
ORDER BY pwl.request_id, pwl.work_list_id;

What I noticed in your query is that your given date does not comply the format you have defined in the to_date function. also you need to use to_char to convert the date format in the query, as follows:

AND PRODUCT_WORK_LIST.value = to_char('2004-05-19', 'yyyy-mm-dd')

you might still get the problem because your table column PRODUCT_WORK_LIST.value is still using the default date format to compare with formatted date, you need to change the format of table column too in order to keep both values in consistent.

AND to_char(PRODUCT_WORK_LIST.value,'yyyy-mm-dd') = to_char('2004-05-19', 'yyyy-mm-dd')

fixed value need to comply the format defined in the to_char function, but if you are using any table column then you just need to define the format, no matter which format is used to store table column value into table.

Thank you, that was helpful, but I should have been more clear. What I have now is:

AND to_date(PRODUCT_WORK_LIST.value, 'mm/dd/yyyy') = to_date('2004-05-19', 'yyyy-mm-dd')

The PRODUCT_WORK_LIST.value field is a string, the comparing value is also a string, but this is input by a user. This means I don't know what format the date is going to take (i.e. 'mm-dd-yyyy', 'yyyy-mm-dd', etc). My stored date, however, is a constant format of 'mm/dd/yyyy'. So effectively it will read:

AND to_date(PRODUCT_WORK_LIST.value, 'mm/dd/yyyy') = to_date(userInputString, 'I don't know what format to use')

How can I do this comparison?

You will need an input mask 4 that field. It's not your job 2 find out what date format the user chosen, your job is 2 make sure that the user can only input data based on input mask (field validation) you specified. Minimize your pain and the probability of getting future errors at the user input level. Give him the chance of making a mistake and you can be sure that he will do it. It's like a Murphy law or maybe it is?

Not sure if there is a reason your input field is a character field, but you could also set the input field datatype to date, remove all format masks, set the max length to 11 and rely on the os registry settings to convert the date to an acceptable format.

Forms will automatically adjust the date format depending on your max length ('25-may-04' for length 11). Only thing is, not sure how different NLS settings will affect your individual needs.

We do this to allow the users to input almost any way they like. Then to compare we just:

AND to_date(PRODUCT_WORK_LIST.value, 'mm/dd/yyyy') = :input_item