removing time element from date on sqlload
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: removing time element from date on sqlload

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    removing time element from date on sqlload

    I've been messing about with this for half an hour now.

    I'm loading from a csv file into a table with a date column, (DATE_OF_BIRTH DATE)

    Input data is e.g. 23/12/1964 00:00

    Getting error "Field in data file exceeds maximum length"

    How do I remove the timestamp on load??

    Been looking at

    REPLACE INTO TABLE temp
    (
    .......
    DATE_OF_BIRTH date(10) "DD/MM/YYYY"
    .......
    )

    but that doesn't seem to work

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    to_date('23/12/1964 00:00','dd/mm/yyyy')
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    to_date('23/12/1964 00:00','dd/mm/yyyy hh24:mi') might work better!

    the ref is: http://www.csee.umbc.edu/help/oracle.../ch05.htm#6132

  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    How does that remove the time element Dapi?

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Ah!

    1) A date field always has a time part, so you can't get rid of it. I had assumed that all your times were 00:00 as in the example.

    2) Try: myfield DATE "to_date(substr(:myfield,1,10),'dd/mm/yyyy')"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width