Converting dates stored as VARCHAR2 to a date

Question

I have seen similar posts to this, but I am not able to resolve my query.

I am trying to query a table that has a column ("VALUE") of VARCHAR2 datatype.

The rows in this column are mixed with both numerical and date values (I do not know why the dates were stored as VARCHAR2).

I only need the dates and I have filtered off the rows with LIKE function.

SELECT
PARENTID,
NAME,
VALUE
FROM TIMINGEVENT
WHERE NAME like 'last%'
;

Now the column only has the dates and I need to convert from VARCHAR2 to date.

PARENTID        ++  NAME            ++  VALUE 

---

1701480         ++  lastCycle1      ++  

1701480         ++  lastCycle2      ++  

1701480         ++  lastCycle3      ++  20150901092520 AM

1701480         ++  lastCycle4      ++  20150901092834 AM

1701480         ++  lastCycle5      ++  20150901085047 AM

My attempts to use TO_DATE resulted in the following error:

ORA-01858: a non-numeric character was found where a numeric was expected

I am using Oracle 11g SQL Developer and the NLS preferences for date format is set to DD-MON-RR.

I found the below approach in another post, but when I use it it throws the below error?

SELECT 
PARENTID,
NAME,
VALUE,
TO_CHAR(TO_DATE(VALUE, 'MM/DD/YYYY'), 'MM/DD/YYYY') AS "test"
FROM TIMINGEVENT
WHERE NAME like 'last%'
;


ORA-01843: not a valid month
01843. 00000 -  "not a valid month"

Answer 1

It seems that you only need the right format:

with test(parentId, name, value) as (
select '1701480','lastCycle1','' from dual union all
select '1701480','lastCycle2','' from dual union all
select '1701480','lastCycle3','20150901092520 AM' from dual union all
select '1701480','lastCycle4','20150901092834 AM' from dual union all
select '1701480','lastCycle5','20150901085047 AM' from dual
)
select to_date(value, 'YYYYMMDDHHMISS AM')
from test

Answer 2

When you use a format specifier inside to_date(), what it does is to try to map the value in the column exactly in the format that has been as format specifier, not something less, not something more.

So, When you use TO_DATE(VALUE, 'MM/DD/YYYY') it tries to map the first 2 characters ie, 20 as MM. Hence it is giving the error as not a valid month.

You need a proper format specifier to deal with the column, as one is shown below -

SELECT TO_DATE(VALUE, 'yyyymmddhhmiss am') FROM TIMINGEVENT

It will give output like -

9/1/2015 9:25:20 AM

Later you can again format the output of this as per your requirement by using to_char and again using a proper format specifier.

SELECT to_char(TO_DATE(VALUE, 'yyyymmddhhmiss am'),'DD-MON-YYYY HH12:MI:SS AM') FROM TIMINGEVENT

This will give an output like -

01-SEP-2015 09:25:20 AM

Please note, it does not matter is your value contains am or pm in it, you can use both am or pm. I mean, ironically it is not mandatory to use 'am' if the value contains 'am' in it. You can use 'pm' too even if the value contains 'am'. Not that it makes much sense in using it that way, still just an FYI.

Answer 3

First understand the usage of to_date and to_char functions:

TO_CHAR: To change other datatypes like date or number to string, if you are changing a date to character, then you must specify the date format to which it should be converted.

TO_DATE To change string/char to date, the second parameter is date format of your string in your case, the date format of the string "20150901092520 AM" is "YYYYMMDDHHMISS AM", so you have use it as

to_date('20150901092520 AM', 'YYYYMMDDHHMISS AM'), this will convert it to date object, now to print it required format as "09/01/2015" use to_char and specify the format as "MM/DD/YYYY" like below

to_char(to_date('20150901092520 AM', 'YYYYMMDDHHMISS AM'),'MM/DD/YYYY')

Answer 4

You should be able to use the ISDATE function to test the string before converting https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1106.htm

Generally... SELECT cust_last_name, CASE value WHEN isdate(value) = 'Yes' THEN convert(date,value) WHEN isdate(value) <> 'Yes' THEN '' END FROM TABLE;