Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18

    Unanswered: Unable to convert string to timestamp

    We have a staging table that's used to load raw data from our suppliers.

    One column is used to capture a time-stamp but its data-type is varchar(265). Data's dirty: about 40% of the time, there is garbage data, otherwise time-stamp data like this

    Code:
        2011/11/15 20:58:48.041
    I have to create a report that filters some dates/timestamps out that column but where I try to cast it, I get an error:
    Code:
        db2 => select cast(loadedon as timestamp) from automation
        
        1
        --------------------------
        SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007
    Is there a way around that? Can you help? Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you have two options: write a stored procedure and handle rows in a loop, catching the exception, or create your own function to convert strings to timestamps while ignoring "garbage".

  3. #3
    Join Date
    Dec 2011
    Posts
    2
    you try this.
    date(to_date(substr(START_TIME,1,8),'yyyymmdd'))

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If the format of valid strings was only 'yyyy/mm/dd hh:mi:ss.fff',
    it might be enough to replace slash("/") to hyphen("-") then to convert it to timestamp.

    Here is an example to convert varchar data with format 'yyyy/mm/dd hh:mi:ss.fff' to timestamp
    and data with other than the format to null.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH 
     automation(loadedon) AS (
    VALUES
      '2011/11/15 20:58:48.041'
    , 'abcdefg'
    , '2011/11/15*20:58:48.041'
    , '20*1/11/15 20:58:48.041'
    , '****/**/** **:**:**.***'
    , '    /  /     :  :  .   '
    , '2011-11-15 20:58:48.041'
    , '2011/11/15 20: 8:48.041'
    , '  2011/11/15 20:58:48.041'
    , '2011/13/15 20:58:48.041'
    , '2011/11/00 20:58:48.041'
    , '2011/02/28 20:58:48.041'
    , '2011/02/29 20:58:48.041'
    , '2012/02/28 20:58:48.041'
    , '2012/02/29 20:58:48.041'
    , '2000/02/28 20:58:48.041'
    , '2000/02/29 20:58:48.041'
    , '2011/11/15 24:00:00.041'
    , '2011/11/15 20:60:48.041'
    , '2011/11/15 20:58:60.041'
    , '2011/11/15 24:00:00.000'
    )
    SELECT loadedon
         , CASE
           WHEN TRANSLATE(loadedon , '+' , '*0123456789' , '*')
                = '****/**/** **:**:**.***'
            AND SUBSTR(loadedon , 1 , 4) BETWEEN '0001' AND '9999'
            AND (    SUBSTR(loadedon , 6 , 2) IN ('01' , '03' , '05' , '07' , '08' , '10' , '12')
                 AND SUBSTR(loadedon , 9 , 2) BETWEEN '01'   AND '31'
                 OR  SUBSTR(loadedon , 6 , 2) IN ('04' , '06' , '09' , '11')
                 AND SUBSTR(loadedon , 9 , 2) BETWEEN '01'   AND '30'
                 OR  SUBSTR(loadedon , 6 , 2) = '02'
                 AND SUBSTR(loadedon , 9 , 2) BETWEEN '01'   AND '28'
                 OR  SUBSTR(loadedon , 6 , 2) = '02'
                 AND SUBSTR(loadedon , 3 , 2)  IN ( '00' , '04' , '08' , '12' , '16'
                                                  , '20' , '24' , '28' , '32' , '36'
                                                  , '40' , '44' , '48' , '52' , '56'
                                                  , '60' , '64' , '68' , '72' , '76'
                                                  , '80' , '84' , '88' , '92' , '96'
                                                  )
                 AND SUBSTR(loadedon , 1 , 4) NOT IN ('1600' , '2000' , '2400')
                 AND SUBSTR(loadedon , 9 , 2) = '29'
                )
            AND (    SUBSTR(loadedon , 12 ,  2) <= '23'
                 AND SUBSTR(loadedon , 15 ,  2) <= '59'
                 AND SUBSTR(loadedon , 18 ,  2) <= '59'
                 OR  SUBSTR(loadedon , 12 , 12) =  '24:00:00.000' 
                )
           THEN
                CAST( REPLACE(loadedon , '/' , '-') AS  TIMESTAMP )
           END  AS valid_timestamp_value
     FROM  automation
    ;
    ------------------------------------------------------------------------------
    
    LOADEDON                  VALID_TIMESTAMP_VALUE     
    ------------------------- --------------------------
    2011/11/15 20:58:48.041   2011-11-15-20.58.48.041000
    abcdefg                   -                         
    2011/11/15*20:58:48.041   -                         
    20*1/11/15 20:58:48.041   -                         
    ****/**/** **:**:**.***   -                         
        /  /     :  :  .      -                         
    2011-11-15 20:58:48.041   -                         
    2011/11/15 20: 8:48.041   -                         
      2011/11/15 20:58:48.041 -                         
    2011/13/15 20:58:48.041   -                         
    2011/11/00 20:58:48.041   -                         
    2011/02/28 20:58:48.041   2011-02-28-20.58.48.041000
    2011/02/29 20:58:48.041   -                         
    2012/02/28 20:58:48.041   2012-02-28-20.58.48.041000
    2012/02/29 20:58:48.041   2012-02-29-20.58.48.041000
    2000/02/28 20:58:48.041   2000-02-28-20.58.48.041000
    2000/02/29 20:58:48.041   -                         
    2011/11/15 24:00:00.041   -                         
    2011/11/15 20:60:48.041   -                         
    2011/11/15 20:58:60.041   -                         
    2011/11/15 24:00:00.000   2011-11-15-24.00.00.000000
    
      21 record(s) selected.
    Last edited by tonkuma; 12-21-11 at 03:27.

Posting Permissions

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