Skip navigation
Contact Us
6326 Views 7 Replies Latest reply: Dec 3, 2013 6:55 PM by Naga Kondapaturi RSS
Parthasaradhi Reddy Newbie 25 posts since
Jun 26, 2006
Currently Being Moderated

Jun 27, 2007 9:21 PM

How to convert string(26) into timestamp


Hi,





I need to convert string(26) 2005-02-28-08.58.42.195332 into timestamp in expression.





Please letme know if anybody knows.





Cheers,

Partha.

  • Kieran O'Loughlin InfaEmp 1,889 posts since
    Feb 26, 2003
    Currently Being Moderated
    Jun 28, 2007 1:51 AM (in response to Parthasaradhi Reddy)
    How to convert string(26) into timestamp


    PowerCenter's data/time only supports to the second so you will lose data if you do this conversion. If you still want to do it, something like this will probably work.



    to_date(substr(infield, 1, 10) || ' ' || substr(infield, 12, 8), 'YYYY-MM-DD HH24.MI.SS')



    It's possible that to_date won't like the '.' character between hour and minute and minute and second. If so then use replacechr to change it to something like ':'.






    -




    1) If you don't have a user at http://my.informatica.com go there and register

    2) Have you searched the manuals? Up to version 7.x they can be found in the manuals subdirectory of the client installation. For version 8.x they are in a separate installation. If you don't have the manuals then you can download them from the documentation center at http://my.informatica.com

    3) Devnet search - search devnet, something similar may well have been asked before

    4) Google search - if the topic is not directly PowerCenter related, but is related to database errors, scripting, operating systems, etc. then there is a wealth of information on the web

    5) Knowledgebase search - search the Knowledgebase at http://my.informatica.com

    6) Velocity methodology - search the Velocity methodology at http://my.informatica.com

    7) If you still have a question give as much background to your issue as possible

    - Include code snippets

    - Include workflow/session logs

    - Include examples of data

    etc.


    http://www.catb.org/~esr/faqs/smart-questions.html

    • Naga Kondapaturi Newbie 7 posts since
      Sep 30, 2012
      Currently Being Moderated
      Dec 19, 2012 10:26 PM (in response to Kieran O'Loughlin)
      How to convert string(26) into timestamp

      I am facing the same issue, your solution might resolve it but I don't understand why the microseconds cannot be converted. US Microseconds (0-999999) is a valid TO_DATE format so why does it still give the error during conversion?

       

      Transformation Evaluation Error [<<Expression Error>> [TO_DATE]: invalid string for converting to Date ... t:TO_DATE(s:'2010-06-18 00:00:00.000',s:'YYYY-MM-DD HH:MI:SS.US')]

       

      In the session properties the default Date Time Format String also consists of the microseconds MM/DD/YYYY HH24:MI:SS.US

       

      Please let me know if you have any info.

    • Naga Kondapaturi Newbie 7 posts since
      Sep 30, 2012
      Currently Being Moderated
      Dec 19, 2012 11:19 PM (in response to Kieran O'Loughlin)
      How to convert string(26) into timestamp

      I did some more research and came to following conclusion. TO_DATE supports microseconds but it expects the data to be non-zeros. In fact the same applies to the entire time string.

       

      For eg:

      TO_DATE ('2011-06-07 06:32:29.267', ''YYYY-MM-DD HH24:MI:SS.US') - works

       

      But if the time is all zeros it fails

      TO_DATE ('2011-06-07 00:00:00.000', ''YYYY-MM-DD HH24:MI:SS.US') - fails

       

      In this case substr without the time resolved my issue.

      TO_DATE (SUBSTR('2011-06-07 00:00:00.000', 0, 10), ''YYYY-MM-DD') - works

       

      I think the integration service fails to read the time if it is all zeros which makes sense because there is no such time in the clock.

  • Naga Kondapaturi Newbie 7 posts since
    Sep 30, 2012
    Currently Being Moderated
    Dec 19, 2012 11:25 PM (in response to Parthasaradhi Reddy)
    How to convert string(26) into timestamp

    I know this is a very old post but just curious did you try -

     

    v_Date = 2005-02-28-08.58.42.195332

     

    TO_DATE(v_Date, 'YYYY-MM-DD-HH:MI:SS.US')

    OR

    TO_DATE(SUBSTR(v_Date, 0, 10)  || ' ' || SUBSTR(v_Date, 12, 12), 'YYYY-MM-DD HH:MI:SS.US')

More Like This

  • Retrieving data ...

Bookmarked By (0)