Having Date format issues with query being run.

[jrdnoland]

I'm trying to run a query that will drop off experiments if the experiment closed date is before the current date.



Using Excel 2019 and Oracle 12g.

SELECT distinct e.EXPERIMENT_NUMBER, e.name,
(SELECT value FROM WORKBOOK_DATA WHERE NAME = 'Instrument' AND WORKBOOK_NUMBER = wb.WORKBOOK_NUMBER), 
(SELECT value FROM WORKBOOK_DATA WHERE NAME = 'Exp_Date' AND WORKBOOK_NUMBER = wb.WORKBOOK_NUMBER) 
FROM EXPERIMENT e JOIN WORKBOOK wb ON wb.EXPERIMENT_NUMBER = e.EXPERIMENT_NUMBER 
JOIN WORKBOOK_DATA wd ON wd.WORKBOOK_NUMBER = wb.WORKBOOK_NUMBER WHERE wd.NAME IN ('Instrument', 'Exp_Date') 
AND ((wd.NAME = 'Exp_Date') AND To_Date(wd.VALUE,'DD/MON/YY') > To_Date(CURRENT_DATE, 'DD/MON/YY') 
OR (wd.NAME = 'Instrument')) AND e.NAME LIKE '%_CAL%' 
AND e.GROUP_NAME IN ('ANALYTICAL_PL_RD','DEVELOPMENT_PL_RD','MICRO_PL_RD','RD_PROCESSRESEARCH','PD_PL_RD','SCS_PL_RD')

I get the error

ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.

How do I get the literals to match? The date format of the Oracle field is DD-MON-RR

[ranman256]

MON is not a format in microsoft.
month format is MM

[jrdnoland]

MON is not a format in microsoft.
month format is MM

Yes, but the oracle database holding the data is DD-MON-RR, Ive tried formatting to DD-MM-YYYY with the same error message. Using To_Date and have also tried SYSDATE to get current date.

Thanks for taking the time to look at my post and respond!

[orange]

Just did some googling and found this link

Sample from the site:
"You convert string to date with a different format. Try this

select to_char(to_date(’25-SEP-14′,’DD-MON-YY’), ‘DD/MM/YYYY’) FROM DUAL"

[jrdnoland]

Just did some googling and found this link

Sample from the site:
"You convert string to date with a different format. Try this

select to_char(to_date(’25-SEP-14′,’DD-MON-YY’), ‘DD/MM/YYYY’) FROM DUAL"

Thank you Orange! Appreciate the help.