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
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.
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.
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.
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(SUBSTR(v_Date, 0, 10) || ' ' || SUBSTR(v_Date, 12, 12), 'YYYY-MM-DD HH:MI:SS.US')
I just tried Nag's solution: TO_DATE(SUBSTR(v_Date, 0, 10) || ' ' || SUBSTR(v_Date, 12, 12), 'YYYY-MM-DD HH:MI:SS.US') , but found that it did not work for 27 char datetime string either. Why does Informatica PowerCenter 9.5 make something so simple to be difficult? It hurt's their brand.
It wouldn't work because the formula will look for only 26 characters. What is your format which is resulting in 27 characters? You will have to adjust the substring accordingly.