Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: Problem with Conversion of data type from Varchar to DB2 Timestamp

    Hi All,

    I have loaded staging tables from excel source. In excel sheet date format is '8/3/2009 12:00:00AM', so in staging i used varchar data type to store this date. Now when i am trying to move it to fact table i am facing problem with data type conversion.

    I have used different functions like to_date, timestamp_format but non of them is working because as we can see in staging table date format is m/d/yyyy hh:mm:ss where as db2 timestamp is yyyy-mm-dd hh:mm:ss.fff.

    I cant even use substring as length of date and month field varies i.e from 1 to 9 it will be 1 and from 10 onwards it will be 2 so substring also not possible.

    Can anyone help me as how can i resolve this issue??

    Thanks in Advance.
    Last edited by abhisheksri85; 04-28-12 at 07:32.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    abhisheksri85, You don't mention what version of DB2 you have but, based on your supplied format of m/d/yyyy hh:mm:ss, TIMESTAMP_FORMAT does work in DB2 LUW V9.7 that I tried it on.
    Code:
    WITH TEST_TAB (TSMP_VC)
      AS (
          SELECT CAST('8/3/2009 12:00:00'   AS VARCHAR(30)) FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CAST('12/5/2010 8:02:50'   AS VARCHAR(30)) FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CAST('11/13/2011 23:54:24' AS VARCHAR(30)) FROM SYSIBM.SYSDUMMY1
         )
    SELECT TSMP_VC
         , TIMESTAMP_FORMAT(TSMP_VC, 'MM/DD/YYYY HH24:MI:SS',6) AS TMSP_COL
    FROM TEST_TAB
    
    TSMP_VC                        TMSP_COL           
    ------------------------------ --------------------------
    8/3/2009 12:00:00              2009-08-03-12.00.00.000000
    12/5/2010 8:02:50              2010-12-05-08.02.50.000000
    11/13/2011 23:54:24            2011-11-13-23.54.24.000000
    
      3 record(s) selected.

Posting Permissions

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