Convert different date formats to one specific

Andro02 · #1 (**permalink**) 06-24-11, 09:28

Good day

,

i have the following problem. i got a table "Employes" with their names, adresses etc and birtday. but the birthday format is different in almost every row, theres not a standard format, for example

- 31.12.1980
- 01.12.65
- 1990/02/12
- 12/12/1992

is there a way to put these different formats into another one for example like the first one?
a select would be enought for the conversion

thank you very much
Pascal

n_i · #2 (**permalink**) 06-24-11, 10:23

Quote:

Originally Posted by Andro02

Good day

,

<...>

- 31.12.1980
- 01.12.65
- 1990/02/12
- 12/12/1992

I'm assuming you store these things as VARCHARs and don't validate the input. I'm afraid the day is not so good for you - there is no easy solution for this.

If you don't have (and cannot buy) any of the off-the-shelf data cleansing tools, you would probably need to "roll your own" function with pattern matching to parse all these various date strings and convert them to a proper DATE datatype.

tonkuma · #3 (**permalink**) 06-24-11, 10:37

I thought that some more information(or data) need in the "Employes" table.
Because, there are two possible format for each sample data except first one, like...
- 31.12.1980
* dd.mm.yyyy
- 01.12.65
* dd.mm.yy
* mm.dd.yy
- 1990/02/12
* yyyy/mm/dd
* yyyy/dd/mm
- 12/12/1992
* mm/dd/yyyy
* dd/mm/yyyy

If you can list up all possible formats and you can specify one format for each data in the table, it would be not so difficult to write a function.

tonkuma · #4 (**permalink**) 06-24-11, 11:00

If the formats for your sample data were
- 31.12.1980
dd.mm.yyyy
- 01.12.65
mm.dd.yy
- 1990/02/12
yyyy/mm/dd
- 12/12/1992
mm/dd/yyyy

then, here is a sample expression for the formats.
(the format of fifth data '1999.02.12' was not in the expected formats, then returned null.)

Code:

------------------------------ Commands Entered ------------------------------
SELECT birthday
     , DATE(
          TIMESTAMP_FORMAT(
             birthday
           , CASE TRANSLATE(birthday , '' , '0123456789' , '*')
             WHEN '**.**.****' THEN 'dd.mm.yyyy'
             WHEN '**.**.**'   THEN 'mm.dd.rr'
             WHEN '****/**/**' THEN 'yyyy/mm/dd'
             WHEN '**/**/****' THEN 'mm/dd/yyyy'
             END
          ) 
       ) AS standard_date
 FROM  (VALUES '31.12.1980'
             , '01.12.65'
             , '1990/02/12'
             , '12/12/1992'
             , '1999.02.12'
       ) employes(birthday);
------------------------------------------------------------------------------

BIRTHDAY   STANDARD_DATE
---------- -------------
31.12.1980 1980-12-31   
01.12.65   1965-01-12   
1990/02/12 1990-02-12   
12/12/1992 1992-12-12   
1999.02.12 -            

  5 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech