DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Converting UTC date format into SQL date field

  1. #1
    Join Date
    Mar 2004
    Location
    Irvine, CA
    Posts
    8

    Converting UTC date format into SQL date field

    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.

  2. #2
    Join Date
    Mar 2004
    Location
    Irvine, CA
    Posts
    8
    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.

  3. #3
    Join Date
    May 2011
    Posts
    1

    Smile Convert UTC date format into SQL DateTime format

    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)

Similar Threads

  1. find version & service packs
    By rperez in forum Database
    Replies: 5
    Last Post: 01-02-2009, 05:14 PM
  2. Replies: 1
    Last Post: 05-30-2001, 06:27 PM
  3. SQL vs NT date handling
    By warwick in forum Database
    Replies: 0
    Last Post: 07-31-2000, 08:11 PM
  4. Converting Text to a Date field
    By Juan in forum VB Classic
    Replies: 1
    Last Post: 07-14-2000, 02:19 PM
  5. Re: ODBC error
    By Devaraj in forum Enterprise
    Replies: 0
    Last Post: 05-11-2000, 12:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center