Introduction
Working with date values stored as text is one of the trickiest things when using SQL. In this tip, I'll share some tips on how you can convert these text values into date values.
Why Use Dates?
There are a few reasons why you should be using date types when working with dates.
First of all, it's a more accurate representation of the data. If you have something that represents a date, it's more accurately represented if it's stored in a date data type.
It also lets you sort on the field properly. Date data types treat the order of dates in the correct order. If it is stored as a string, it's sorted in ascending order based on the text value inside (e.g. '01/01/2015' would come before '02/01/2010').
You can also perform more tasks and functions on date fields. You can extract parts of date, perform date arithmetic, and even group on dates easier than if it was stored as a text field.
How to Convert Text into Date
So, what if you have some date information stored in a text field? Let's have a look at this, and see how we can convert it to a date in Oracle SQL.
First, let's set up a table with some date values stored as text.
CREATE TABLE date_test (
test_id NUMBER,
date_as_text VARCHAR2(20),
date_value DATE);
INSERT INTO date_test (test_id, date_as_text) VALUES (1, '01-JAN-2015');
INSERT INTO date_test (test_id, date_as_text) VALUES (2, 'Feb 10, 2015');
INSERT INTO date_test (test_id, date_as_text) VALUES (3, 'MAR 20 2014');
INSERT INTO date_test (test_id, date_as_text) VALUES (4, '06/04/2015');
SELECT test_id, date_as_text, date_value
FROM date_test;
| TEST_ID | DATE_AS_TEXT | DATE_VALUE |
| 1 | 01-JAN-15 | NULL |
| 2 | Feb 10, 2015 | NULL |
| 3 | MAR 20 2014 | NULL |
| 4 | 06/04/2015 | NULL |
I've created a table with two columns and four records, each of which have a different format. There's also a test_id field for referring to individual records later. It's a terrible design, but it's just for this example.
The CAST Function
We can try to convert the data to a DATE type by using the CAST function. It takes an input of the value as well as the data type. It doesn't let you specify the format, though, so let's see what happens.
SELECT test_id, CAST(date_as_text AS DATE)
FROM date_test;
ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
If I run the CAST statement on each test_id row, I get different results.
SELECT test_id, CAST(date_as_text AS DATE)
FROM date_test
WHERE TEST_ID = 1;
| TEST_ID | CAST(DATE_AS_TEXTASDATE) |
| 1 | 1/JAN/15 |
| 2 | Error: non-numeric character |
| 3 | Error: non-numeric character |
| 4 | Error: not a valid month |
The CAST function is good, but it doesn't always help.
The TO_DATE Function
Another way I can do this is to use the TO_DATE function.
This function is more powerful, as it lets you choose the format of the incoming data, unlike CAST.
In my example, all of my dates are in a different format, so I'll have to specify them individually. If I don't, I'll get this error.
SELECT test_id, TO_DATE(date_as_text)
FROM date_test;
ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
I'll need to select them individually.
SELECT test_id, TO_DATE(date_as_text)
FROM date_test
WHERE test_id = 4;
| TEST_ID | CAST(DATE_AS_TEXTASDATE) |
| 1 | 1/JAN/15 |
| 2 | Error: non-numeric character |
| 3 | Error: non-numeric character |
| 4 | Error: not a valid month |
Why did these fail?
It's because TO_DATE didn't recognise the format. TO_DATE has a default format, which is based on your location (or the location of the database).
You can specify the format to use instead, though. Let's try that.
You need to type in the format to use as the second parameter in the TO_DATE function. This is the format of the date in the first parameter.
SELECT test_id, TO_DATE(date_as_text, 'DD-MON-YYYY') as TODATE_VALUE
FROM date_test
WHERE test_id = 1;
| TEST_ID | TODATE_VALUE |
| 1 | 1/JAN/15 |
SELECT test_id, TO_DATE(date_as_text, 'MON DD,YYYY') as TODATE_VALUE
FROM date_test
WHERE test_id = 2;
| TEST_ID | TODATE_VALUE |
| 2 | 10/FEB/15 |
SELECT test_id, TO_DATE(date_as_text, 'MON DD YYYY') as TODATE_VALUE
FROM date_test
WHERE test_id = 3;
| TEST_ID | TODATE_VALUE |
| 3 | 20/MAR/14 |
SELECT test_id, TO_DATE(date_as_text, 'DD/MM/YYYY') as TODATE_VALUE
FROM date_test
WHERE test_id = 4;
| TEST_ID | TODATE_VALUE |
| 4 | 06/APR/15 |
You can see that all of the queries have the dates returned. They are all in the same data type.
Now, let's update the original table to set the date_value to the date version of each value.
UPDATE date_test
SET date_value = TO_DATE(date_as_text, 'DD-MON-YYYY')
WHERE test_id = 1;
UPDATE date_test
SET date_value = TO_DATE(date_as_text, 'MON DD,YYYY')
WHERE test_id = 2;
UPDATE date_test
SET date_value = TO_DATE(date_as_text, 'MON DD YYYY')
WHERE test_id = 3;
UPDATE date_test
SET date_value = TO_DATE(date_as_text, 'DD/MM/YYYY')
WHERE test_id = 4;
SELECT test_id, date_as_text, date_value
FROM date_test;
| TEST_ID | DATE_AS_TEXT | DATE_VALUE |
| 1 | 01-JAN-2015 | 01/JAN/15 |
| 2 | Feb 10, 2015 | 10/FEB/15 |
| 3 | MAR 20 2014 | 20/MAR/14 |
| 4 | 06/04/2015 | 06/APR/15 |
So, as you can see, all of the date values are now in the original table and stored as date. This makes them easier to process, query, and sort.
