Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Converting datetime

    Hi

    Hope someone can advise.

    I'm getting the date and time in two separate columns in a CSV file in the format:

    A_DATE A_TIME
    29/05/2012 15:30:00

    Now, I need to load this data into a 10g database where the data type of the column is 'date'.

    Need some help to come up with the formula to apply on the data above.

    Something like

    Code:
    select TO_DATE(A_DATE||A_TIME) from dual
    I need to somehow get rid of the '/' and ":" and concatenate the two.

    Regards
    Shajju

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    try this:
    Code:
    to_date(a_date || ' ' || a_time,'DD/MM/YYYY HH24:MI:SS')
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Brilliant! Thanks.

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Hi again

    I used the query and it worked like a charm. However, when I tried to load the data, I'm getting:

    ORA 01861: Literal does not match format string

    NLS_DATE_FORMAT: DD-MM-RR
    NLS_DATE_LANGUAGE: AMERICAN

    Note: If I have a string in the format '20120530080000' and I convert it to datetime using TO_DATE, it loads fine.

    Any ideas?

    Regards
    Shajju

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Some more info, if it helps:

    I've read somewhere that applying the TO_DATE function to a date can cause this.

    I'm loading data using an external table and in the external table A_DATE and A_TIME are set a VARCHAR2(500).

    So when I use TO_DATE(A_DATE || ' ' || A_TIME,'DD/MM/YYYY HH24:MIS'), the result is loaded into a DATE data type column.

    I've even tried:

    TO_DATE(A_DATE || ' ' || A_TIME,'DD/MM/RR HH24:MIS') but to no avail.

    Well almost, till the error mentioned pops up.

  6. #6
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    ORA 01861: Literal does not match format string
    If you get this, your date - format is different than you specified

    Here you find a description of the date format models
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    I was wondering if I could convert:

    A_DATE A_TIME
    30/05/2012 09:30:00

    to a string first and then use TO_DATE on that string?

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by shajju View Post
    I was wondering if I could convert:

    A_DATE A_TIME
    30/05/2012 09:30:00

    to a string first and then use TO_DATE on that string?
    what datatype is above data now?
    it appears to me to be a string as displayed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    I've read somewhere that applying the TO_DATE function to a date can cause this.
    Generally speaking, this conclusion is wrong. The main reason is the mismatch between format mask and actual string value. It may happen more often when implicit conversion (NLS_DATE_FORMAT format mask e.g. when using TO_DATE on DATE data type) is used, but it is not this case.

    Is '20120530080000' value (you posted in previous post) also in 'DD/MM/YYYY HH24:MI:SS' DATE format?
    Although this concrete value will not fail (but see the actual DATE it represents), some other values in different format may act so.
    Code:
    SQL> alter session set nls_date_format = 'yyyy-mm-dd';
    
    Session altered.
    
    SQL> select TO_DATE('30/05/2012 08:00:00','DD/MM/YYYY HH24:MI:SS') from dual;
    
    TO_DATE('3
    ----------
    2012-05-30
    
    SQL> select TO_DATE('20120530080000','DD/MM/YYYY HH24:MI:SS') from dual;
    
    TO_DATE('2
    ----------
    0530-12-20
    
    SQL> select TO_DATE('201205300800000','DD/MM/YYYY HH24:MI:SS') from dual;
    select TO_DATE('201205300800000','DD/MM/YYYY HH24:MI:SS') from dual
                   *
    ERROR at line 1:
    ORA-01861: literal does not match format string
    So, you should not blame Oracle, but the one(s) who created that file, as it contains A_DATE and/or A_TIME fields in multiple formats.

  10. #10
    Join Date
    Aug 2008
    Posts
    464
    Thanks and yes, '20120530080000' value is in 'DD/MM/YYYY HH24:MIS' DATE format and using the TO_DATE function, it gets loaded correctly.

    However, recently due to an upgrade the files now sometimes contain extra zeros at the end and hence the file doesn't load. But the same file also has the A_DATE (30/05/2012) and A_TIME (08:00:00) columns so I was hoping to use them to insert the datetime into the database.

    The header that contains '20120530080000' is 'Startingdatetime' and I use:
    TO_DATE(Startingdatetime,'YYYYMMDDHHMISS')
    to convert '20120530080000' to the datetime: 30/05/2012 08:00:00.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    09:22:44 SQL> select TO_CHAR(to_date('20120530080000','YYYYMMDDHH24MISS'),'DD/MM/YYYY HH24:MI:SS') FROM DUAL;
    
    TO_CHAR(TO_DATE('20
    -------------------
    30/05/2012 08:00:00
    
    09:22:53 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Aug 2008
    Posts
    464
    Thank you Anacedent but will this load into a column with DATE data type?

  13. #13
    Join Date
    Aug 2008
    Posts
    464
    Thanks but I'm not after loading the string '20120530080000' as the datetime. This is what I'm currently doing but the string mentioned above now contains extra digits at the end (probably mili & micro seconds) and therefore those rows do not load.

    I have 2 other columns in the CSV file, which I'd like to use to load the datetime into the table.

    A_DATE A_TIME
    02/06/2012 09:30:00

    But using:
    TO_DATE(A_DATE || ' ' || A_TIME,'DD/MM/YYYY HH24:MIS') gives me

    ORA-01861: Literal does not match format string

    PARAMETER VALUE

    NLS_DATE_FORMAT DD/MM/RR
    NLS_TIME_FORMAT HH12:MISXFF PM
    NLS_TIMESTAMP_FORMAT DD/MM/RR HH12:MISXFF PM
    NLS_TIME_TZ_FORMAT HH12:MISXFF PM TZR
    NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH12:MISXFF PM TZR
    Last edited by shajju; 06-02-12 at 03:33.

  14. #14
    Join Date
    Aug 2008
    Posts
    464
    Could I also add that I'm loading data into the 10g db using external tables in which all header values are configured to be of data type VARCHAR2.

    I can run

    select TO_DATE('30/05/2012' || '' || '09:30:00','DD/MM/YYYY HH:MIS') from dual

    in Toad fine.

    But using:

    select TO_DATE(A_DATE || '' || A_TIME,'DD/MM/YYYY HH24:MIS') from dual

    gives me the error.

  15. #15
    Join Date
    Aug 2008
    Posts
    464

    Problem solved

    Thanks for all your input guys. I found the discrepancy.

    Basically, I was concentrating on:

    to_date(A_DATE || ' ' || A_TIME,'DD/MM/YYYY HH24:MIS')

    But when I tried:

    to_date(A_DATE || ' ' || A_TIME,'YYYY/MM/DD HH24:MIS')

    the files started getting loaded.

Posting Permissions

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