CODEWITHSUNDEEP
sqloracle-databasedate-formatting
Muhammad Sarmad Mahmood Malik
Muhammad Sarmad Mahmood Malik

Reputation: 437

ORA-01843: not a valid month with data type conversion

I have created a view where one of column command is:

TO_CHAR( TO_DATE(sysdate ||' '||TIMING.TIME,'dd-MON-RRRR HH:MIAM'),'dd-MON-RRRR HH:MIAM') as time

The value of TIMING.TIME is like this: 09:30AM as varchar2

When I run the query: select TO_DATE(time,'DD-MON-RRRR HH:MIAM')from view

I get the error

ORA-01843: not a valid month

NLS Language is American.

Upvotes: 2

Views: 1984

Answers (2)

Tatiana
Tatiana

Reputation: 1499

sysdate stores time and it would be better to convert it to char before concatenation

TO_CHAR(sysdate,'dd-MON-RRRR')||' '||TIMING.TIME

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

TO_DATE(sysdate

That is wrong.

Never apply TO_DATE on DATE data type. It forces Oracle to:

  • first convert it into a string
  • then convert it back to date

based on the locale-specific NLS settings. You need TO_DATE to convert a literal into date. For date-arithmetic, leave the date as it is.

If you are trying to configure the time portion in current date, then: 1. First convert the date into string 2. Then concatenate the time portion to the string 3. Finally apply TO_DATE

For example,

SQL> alter session set nls_date_format='DD-MM-YYYY HH:MI:SS AM';

Session altered.

SQL> SELECT to_date(TO_CHAR(sysdate, 'mm/dd/yyyy')
  2    ||' '
  3    ||'09:30AM', 'mm/dd/yyyy hh:miAM') TIME
  4  FROM dual;

TIME
----------------------
14-10-2015 09:30:00 AM

Remember,

  • TO_DATE is used to convert a string into date.
  • TO_CHAR is used to display the date in desired string format.

Modified query for you:

to_date(TO_CHAR(sysdate, 'mm/dd/yyyy') ||' ' ||TIMING.TIME, 'mm/dd/yyyy hh:miAM')
AS "TIME"

Upvotes: 3

Related Questions