Converting UTC date format into SQL date field

[irvinesbest]

I have a delimited text file which has certain text fields containing strings with date and time information. Instead of the standard 'YYYY-MM-DD HH24:MI:SS' format, these strings are in UTC (Coordinated Universal Time) format -- 'YYYY-MM-DDTHH24:MI:SS' -- the only difference being the inclusion of 'T' as a separator. For example, one such value appears in the text file as
'2005-12-07T14:25:50'

My objective is to import this text file into an Oracle table via a control file. The appropriate fields in the table have been defined as being type Date. The control file instructs the program to read the aforementioned strings as date fields. However, I cannot seem to find the correct format that accounts for the 'T' separator. I've tried a few different ones, including 'YYYY-MM-DDTHH24:MI:SS', but none has worked.

I suppose as a last resort I could read in these specific fields as text fields, then execute a function which would strip out the 'T' separator. But I'm wondering if anyone out there knows of a simpler solution.

[irvinesbest]

Well, in case anyone is curious, the solution turns out to be quite simple. Use the following format:

'YYYY-MM-DD"T"HH24:MI:SS'

It's important that you place double quotes around the character T and single quotes around the entire format. The result is a datetime value without the character T.

Also, if the value being imported happens to be just 'T', the following statement should be used in your control file:

...
<fieldname> DATE 'YYYY-MM-DD"T"HH24:MI:SS' "decode(:<fieldname>, 'T', null, null, null, :<fieldname>)",
...

This tells SQL*Loader to assign a null value if <fieldname>='T' or <fieldname> is null. Otherwise, import the value as is.

Hope this helps someone out there, especially if you're dealing with lab data in CDISC format.

[Sharmiladevik]

You can filter and take out only datetime and remove timezone and cast it into datetime.
Eg: select cast(left('2009-07-17T14:23:10-05:00',19) as datetime)