Oracle SQL to_date function not working on concatenated strings

I've been looking for a reason that this isn't working for quite some time. I'm concatenating two fields and attempting to run some date comparisons but it throws the error ORA-01843: not a valid month. I'm not sure what I'm doing wrong here.

Here's my code:

SELECT
   sm.semester_date || cp.start_year AS effective
FROM
   database.table cp,
   database.table2 sm
WHERE   cp.semesters_id = sm.semesters_id 
AND  to_date(sm.semester_date || cp.start_year, 'MM/DD/YYYY') >= to_date('06/01/2011', 'MM/DD/YYYY')

It runs fine as long as I don't add that AND statement at the end. But I need to filter the dataset.

When you run it without the filter it returns 08/15/2010 etc.

I forgot to add the mask originally when I posted this, I've corrected that. However, it still returns this error ORA-01840: input value not long enough for date format.

EDIT SOLUTION FOUND: Firstly, thanks to everyone for helping me with this, you guys are great. Secondly, my error was being caused by a course without a start year. Very frustrating since that shouldn't be able to happen. Because the concatenated item had no year on it, it was throwing the error开发者_运维百科. I only found this because you guys helped me fix my code up. Thank you.

Try adding a separator between date and year:

SELECT
   sm.semester_date || '/' || cp.start_year AS effective
FROM
   database.table cp,
   database.table2 sm
WHERE   cp.semesters_id = sm.semesters_id 
AND  to_date(sm.semester_date || '/' || cp.start_year) >= to_date('06/01/2011', 'MM/DD/YYYY')

If you run the query without the filter, what does it return?

My guess is that your default date format does not match the format of the string that you are passing to TO_DATE. Add a format string as a second parameter to the call (as you've done for the second call) to specify what the format of the concatenated string is.

Is the SEMESTER_DATE column a VARCHAR2? And if so, is it a string in the format 'MM/DD/'? Or just 'MM/DD'?

As lweller points out, the first TO_DATE is also missing the format mask which would be a problem if your session's NLS_DATE_FORMAT is anything other than 'MM/DD/YYYY'.

Assuming SEMESTER_DATE is a VARCHAR2 in the format 'MM/DD', I suspect you want

AND to_date( sm.semester_date || '/' || cp.start_year, 'MM/DD/YYYY' ) >= 
      to_date( '06/01/2011', 'MM/DD/YYYY' )

Your first TO_DATE() relies in the system's default format. Provide a second argument with a proper format string, such as 'MM/DD/YYYY'. Please compare:

SELECT SEMESTER_DATE || START_YEAR AS BARE_STRING, TO_DATE(SEMESTER_DATE || START_YEAR) AS CASTED_TO_DATE
FROM (
    SELECT '12/31' AS SEMESTER_DATE, 2010 AS START_YEAR
    FROM DUAL
);

SELECT SEMESTER_DATE || START_YEAR AS BARE_STRING, TO_DATE(SEMESTER_DATE || START_YEAR, 'MM/DD/YYYY') AS CASTED_TO_DATE
FROM (
    SELECT '12/31' AS SEMESTER_DATE, 2010 AS START_YEAR
    FROM DUAL
);