Use this format:
To_Date( event_instances_start, 'YYYY-MM-DD"T"HH24:MI:SS.US')
That means: your string is almost correct, you just have to replace the blank between date and time parts by a double quote followed by the uppercase T for the timezone followed by another double quote.
This "trick" with the double quotes to quote fixed strings in a format description is described in the Transformation Language guide for PowerCenter in the chapter about To_Date() and To_Char(date) format strings. It means that the string enclosed in double quotes will simply be skipped in the date/time string.
HI, i need help with a related conversion.
I need to convert this string expression to a date format:
date/time EFFECTIVE_DATE = 06/12/2021 00:00:00.000000
string TO_CHAR(EFFECTIVE_DATE,'MM/DD/YYYY') || 'T04:00:00' -> 06/12/2021T04:00:00
But trying to convert to date/time:
To_Date((TO_CHAR(EFFECTIVE_DATE,'MM/DD/YYYY') || 'T04:00:00'), 'YYYY-MM-DD"T"HH24:MI:SS.US')
The output is: 12/20/0006 01:04:00.000000
Is change month, day, year
Not clear what you need. Do you have an input string which you want to convert to a Date/Time?
Hello , I am using informatica 10.4.1.
I have similar requirement where i am getting the date from a file as 2021-04-15T13:21:14.000-05:00
when i tried to use the same way as you mentioned i am still getting invalid date format to conversion this is how i am going in the informatica, not sure where i am going the mistake
i_enddate = 2021-04-15T13:21:14.000-05:00
The point is that the format string must match the format of the input string exactly. Which is not the case here because your input string bears this appended timezone shift "-05:00".
You have to find out whether an input string contains such a shift and, if yes, "remove" it (e.g. by using the SUBSTR() function to use only the part before the "-").
The string before the "-" sign can then become input to TO_DATE().
Sorry for not understanding, in the original question posted, the date string mentioned is "2018-12-30T16:00:00-05:00"
My Date string is i_enddate = 2021-04-15T13:21:14.000-05:00
I think both are same format
I did not exactly understand the difference here
and when your reply to my question, remove the shift, you mean use substring and get 2021-04-15T13:21:14.000 and then convert?
That's exactly what I meant.
And yes, you are right, I totally overlooked the -05:00 in the original post. Thanks for pointing it out.