Oracle to_date returns an error 01843

Question

When trying to do select statement it returns an error. Formatting seems right. Please point where am I mistaken.

SELECT to_date (CALL_DATE,'DD-MON-YYYY HH24:MI') AS DATET FROM XXXXXX

where CALL_DATE is a VARCHAR2 type column, where date is stored as 12-Jan-2017 12:04.

It returns me

00000 - "not a valid month".

Someone has stored something that's not a real date in your column. If you **can** change this column to an actual date, you should. Try querying the column with `select distinct substr(CALL_DATE, 4,3) from MyTable` to find what's wrong — JohnHC, Mar 03 '17 at 08:39
Use proper column data types, and you' wont run into this problem again... — jarlh, Mar 03 '17 at 08:43

score 1 · Accepted Answer · answered Mar 03 '17 at 09:47

1

If you use to_date that way, you're relying on the language of your environment.

If you use 'Jan' you're assuming that you're using a language where January may be written as 'Jan', so you may need to explicitly set the language to use to interpret the dates.

For example, say my language is Italian, so that 'January' is written as 'Gen' and not 'Jan'; if a try your query, I get the same error:

SQL> SELECT to_date ('12-Jan-2017 12:04', 'DD-MON-YYYY HH24:MI') AS DATET
  2  FROM DUAL;
SELECT to_date ('12-Jan-2017 12:04', 'DD-MON-YYYY HH24:MI') AS DATET
                *
ERROR at line 1:
ORA-01843: not a valid month

If I explicitly say the language to use to interpret the stored dates, I have:

SQL> SELECT to_date ('12-Jan-2017 12:04', 'DD-MON-YYYY HH24:MI', 'NLS_DATE_LANGUAGE = English') AS DATET
  2  FROM DUAL;

DATET
---------
12-GEN-17

answered Mar 03 '17 at 09:47

Aleksej

22,443
5
33
38

Thanks, that helped a lot. When I've added to_date('CALL_DATE', 'DD-Mon-YYYY HH24:MI', 'NLS_DATE_LANGUAGE = English') it started working normally. – Fuad Mehdiyev Apr 25 '17 at 09:32
One more question. Is it correct to use sql update with regexp_like ? The following returns an error: update XXXXX set CALL_DATE_1 = to_date(CALL_DATE, 'DD-Mon-YYYY HH24:MI', 'NLS_DATE_LANGUAGE = English') where REGEXP_LIKE (CALL_DATE, '^\d{2}-[A-Z][a-z][a-z]-\d{4} \d{2}:\d{2}$'); – Fuad Mehdiyev Apr 25 '17 at 13:10
There is no problem in using regexp_like in an update statement. What error are you having? Also, [here](http://stackoverflow.com/help/someone-answers) you find something on what to do when someone answers you. – Aleksej Apr 25 '17 at 20:51
Well, the situation is following. Table with varchar2 column CALL_DATE, and DATE column CALL_DATE_1. Task is to copy values from CALL_DATE to CALL_DATE_1. CALL_DATE has values like 13-Mar-2017 22:36 and like 25/01/2017 09:57:00. When I do: update XXXX set CALL_DATE_1 = to_date(CALL_DATE, 'DD-Mon-YYYY HH24:MI', 'NLS_DATE_LANGUAGE = English') where REGEXP_LIKE (CALL_DATE, '^\d{2}-[A-Z][a-z][a-z]-\d{4} \d{2}:\d{2}$') it gives me ORA-01843 error. Same with regexp_like pattern: '^\d{2}/\d{2}/\d{4} \d{2}:\d{2}:\d{2}$' – Fuad Mehdiyev Apr 26 '17 at 11:31

score 0 · Answer 2 · answered Mar 03 '17 at 08:44

0

If the date in the database is 12-Jan-2017 12:04, then your query should be

SELECT to_date (CALL_DATE,'DD-Mon-YYYY HH24:MI') AS DATET FROM XXXXXX

instead of

SELECT to_date (CALL_DATE,'DD-MON-YYYY HH24:MI') AS DATET FROM XXXXXX

answered Mar 03 '17 at 08:44

Stefano Zanini

5,876
2
13
33

This will both give error or work, depending on the language of the OP's DB. – Aleksej Mar 03 '17 at 11:45

score 0 · Answer 3 · answered Mar 03 '17 at 08:44

0

SELECT to_date ('12-Jan-2017 12:04','DD-MON-YYYY HH24:MI') AS DATET FROM DUAL;

returned

DATET

12-JAN-17

shouldn't be a problem with function call if the CALL_DATE is truly in the format '12-Jan-2017 12:04'.

use

SELECT to_date (RTRIM(LTRIM(CALL_DATE)),'DD-MON-YYYY HH24:MI') AS DATET FROM XXXXXX.

answered Mar 03 '17 at 08:44

AbhishekK

91
5

What should the TRIMs do? As an aside, TRIM would do the same with a single function call – Aleksej Mar 03 '17 at 11:47

score 0 · Answer 4 · answered Mar 03 '17 at 11:03

0

thank you all for help. The problem was that some users were storing date incorectly.

answered Mar 03 '17 at 11:03

Fuad Mehdiyev

13
6

score 0 · Answer 5 · edited Apr 10 '17 at 07:17

0

Select to_char(to_date(call_time,'dd-mon-yyyy hh:mi'),'dd-mon-yyyy hh:mi') from xxxx;

edited Apr 10 '17 at 07:17

Pang

9,564
146
81
122

answered Mar 03 '17 at 18:13

Gujjula Paramesh

45
1
2
10

Oracle to_date returns an error 01843

5 Answers5

DATET