ORA-01843: not a valid month error when using to_date function

Question

SELECT sysdate as today,
       to_date(CONCAT (to_char(sysdate,'MM-DD-YYYY'),
               to_char(sysdate,'hh24:mi:ss'))) as time
FROM DUAL

Every time I execute this query it gives me ORA-01843: not a valid month

Could someone please help me?

Answer 1

to_date(CONCAT (to_char(sysdate,'MM-DD-YYYY'), to_char(sysdate,'hh24:mi:ss'))) as time

It is simply useless what you are doing. You are extracting date and time portions separately and then converting it back to DATE. It is nothing but SYSDATE itself.

It would make sense if you are extracting and displaying the date and time elements. Or, you have date and time as string literals separately, and now you want to convert it into a DATE.

For example,

SQL> alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';

Session altered.

SQL> SELECT to_date(CONCAT ('01/11/2016', '14:45:20'), 'MM/DD/YYYYHH24:MI:SS') my_date
  2  FROM DUAL;

MY_DATE
-------------------
01/11/2016 14:45:20

ORA-01843: not a valid month

Reason

This mostly happens due to NLS dependency. Since you are not using an explicit format mask, Oracle is trying to implicitly convert it based on your locale-specific NLS settings.

Example

Let's change the NLS format of the session:

SQL> alter session set nls_date_format='dd/mon/yyyyhh24:mi:ss';

Session altered.

Let's execute the same query:

SQL> SELECT to_date(CONCAT ('01/11/2016', '14:45:20')) my_date
  2  FROM DUAL;
SELECT to_date(CONCAT ('01/11/2016', '14:45:20')) my_date
               *
ERROR at line 1:
ORA-01843: not a valid month

As expected, it throws ORA-01843: not a valid month.

Answer 2

As I understand, you want to see time from sysdate, if so you need this select:

SELECT sysdate as today,
       to_char(sysdate,'MM-DD-YYYY hh24:mi:ss'))) as time
FROM DUAL

In your example wrong part is to_date, you should use mask for to_date:

to_date(CONCAT (to_char(sysdate,'MM-DD-YYYY'),
               to_char(sysdate,'hh24:mi:ss')),'MM-DD-YYYY hh24:mi:ss')

But this will return exactly the same as sysdate because both of this columns are date and the format of dates depends on variable NLS_DATE_FORMAT, check here