Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Unanswered: Convert different date formats to one specific

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Andro02 View Post
    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.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 06-24-11 at 12:28. Reason: Change pad-character for TRANSLATE function to '*'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •