Changing date in Str format to Datetime format in SQL

Question

Really basic question but i have zero experience with SQL. I'm using Tableau to do visualisation with data stored in my company's Oracle server, which contains multiple sheets. The primary table i am working with is named YQ005. One of the fields in the primary table I'm working with contains dates but stored as a String in YYYYMMDD format.

I have to convert this to Date format but doing it through Tableau raises the error "ORA-01843: Not a valid month". How can i do a custom SQL query to select this field, convert it to Date-time format and place this new data in a new column?

Answer 1

Littlefoot has a solid answer but it is definitely not for the inexperienced.

The basic function to convert the string to a date is:

select to_date(yyyymmdd, 'yyyymmdd')

If you are having problems with the month, you can just extract it out to check it:

select (case when substr(yyyymmdd, 5, 2) between '01' and '12'
             then to_date(yyyymmdd, 'yyyymmdd')
        end)

You can also add a check that the value is all numbers:

select (case when regexp_like(yyyymmdd, '^[0-9]{8}') and
                  substr(yyyymmdd, 5, 2) between '01' and '12'
             then to_date(yyyymmdd, 'yyyymmdd')
        end)

Validating dates in Oracle gets much more complicated if you have to validate the whole date -- each month has a different number of days and leap years further complicate matters. But months should always be between 01 and 12.

Answer 2

Error you got means that some values in that table - on 5th and 6th place - don't have a valid month value. For example, it might be 20191823 (there's no 18th month, is there?).

Unfortunately, that's what happens when people store dates as strings. There's no easy way out. If you want to do it with SQL only, you might fail or succeed (if you're VERY lucky). For example, have a look at this example:

SQL> desc yq005
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 DATUM                                              VARCHAR2(8)

SQL> select * From yq005;

DATUM
--------
20191221
13000815
00010101
19302533    -- 25th month and 33rd day
2013Ab12    -- Ab month
2ooo0513    -- year with letters "o" instead of digits "0"

6 rows selected.

SQL>

A query whose where clause tries to identify invalid values:

SQL> alter session set nls_date_format = 'dd.mm.yyyy.';

Session altered.

    SQL> select to_date(datum, 'yyyymmdd') result
      2  from yq005
      3  where substr(datum, 1, 4) between '0000' and '9999'
      4    and substr(datum, 5, 2) between '00'   and '12'
      5    and substr(datum, 7, 2) between '01'   and '31'
      6    and regexp_like(datum, '^\d+$');

    RESULT
    -----------
    21.12.2019.
    15.08.1300.
    01.01.0001.

    SQL>

• lines #3, 4 and 5 are trying to identify valid year/month/day. The first two are OK, more or less, but - it'll miserably fail on at least half of all months because e.g. 20191131 is "valid", but there are no 31 days in November
• line #6 eliminates values that aren't all digits

Just to check that 20191131:

SQL> insert into yq005 values ('20191131');

1 row created.

SQL> select to_date(datum, 'yyyymmdd') result
  2  from yq005
  3  where substr(datum, 1, 4) between '0000' and '9999'
  4    and substr(datum, 5, 2) between '00'   and '12'
  5    and substr(datum, 7, 2) between '01'   and '31'
  6    and regexp_like(datum, '^\d+$');
ERROR:
ORA-01839: date not valid for month specified



no rows selected

SQL>

As I said, it won't work; the same goes for other 30-days months, as well as February.

You could try to create a function which converts string to date; if it succeeds, fine. If not, skip that value:

SQL> create or replace function f_valid_date_01 (par_datum in varchar2)
  2    return number
  3  is
  4    -- return 1 if PAR_DATUM is a valid date; return 0 if it is not
  5    l_date date;
  6  begin
  7    -- yyyymmdd is format you expect
  8    l_date := to_date(par_datum, 'yyyymmdd');
  9    return 1;
 10  exception
 11    when others then
 12      return 0;
 13  end;
 14  /

Function created.

SQL>

Let's use it:

SQL> select datum original_value,
  2         to_char(to_date(datum, 'yyyymmdd'), 'dd.mm.yyyy') modified_value
  3  from yq005
  4  where f_valid_date_01 (datum) = 1;

ORIGINAL MODIFIED_V
-------- ----------
20191221 21.12.2019
13000815 15.08.1300
00010101 01.01.0001

SQL>

Just the opposite - fetch invalid dates:

SQL> select datum
  2  from yq005
  3  where f_valid_date_01 (datum) = 0;

DATUM
--------
19302533
2013Ab12
2ooo0513
20191131

SQL>

This is just one option you might use; there certainly are others, just Google for them. The bottom line is: always store dates into a DATE datatype column and let the database take care about (in)valid values.

[EDIT: how to populate a new column with a valid date]

If there's no date datatype column in the table, add it:

SQL> alter table yq005 add new_datum date;

Table altered.

Now run the update; mind the where clause:

SQL> update yq005 set
  2    new_datum = to_date(datum, 'yyyymmdd')
  3    where f_valid_date_01(datum) = 1;

3 rows updated.

SQL> select * From yq005;

DATUM    NEW_DATUM
-------- -----------
20191221 21.12.2019.
13000815 15.08.1300.
00010101 01.01.0001.
19302533
2013Ab12
2ooo0513
20191131

7 rows selected.

SQL>

Answer 3

The best solution would be to have whoever maintains your database alter the table definition to store dates using the DATE datatype instead of some form of string.

But if you can't or don't wish to alter the Oracle schema, then I would try using the DATEPARSE() function in Tableau, as follows (assuming your date field is named XXX_DATE)

1. In Tableau, rename XXX_DATE to XXX_DATE_ORGINAL
2. Define a calculated field called XXX_DATE as DATEPARSE("YYYYMMdd", [XXX_DATE_ORIGINAL])
3. Hide the original field XXX_DATE_ORIGINAL

Now you can use your XXX_DATE field as a date in Tableau

The renaming and hiding of the original field is not strictly necessary. I just find it helps keep the data source understandable. For more info, see the Tableau online help for DateParse