Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2011
    Posts
    17

    Unanswered: Problem with INSERT INTO and TO_DATE()

    Hello again,

    Have searched all over the internet and cannot seem to find the answer to this problem

    Code:
    INSERT INTO IMPORT_TEMP VALUES('A297POO','ASTRA','500','Douglas Michael',TO_DATE('05/02/2010 09:45:00','DD/MM/YYYY HH/MI/SS'),TO_DATE('07/02/2010 09:45:00','DD/MM/YYYY HH/MI/SS'));
    It has to be something obvious im sure. (tends to be the case)
    The row is being inserted into the table, however the date values are only being stored like this (05-FEB-10).
    Problem is I realistically would like it to be stored like this (05-FEB-2010 09:45)

    Thanks in advance,

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The dates are stored correctly, it's just your client program (e.g. SQL*Plus) which does not show the time part.
    Last edited by shammat; 06-09-11 at 08:18.

  3. #3
    Join Date
    Jun 2011
    Posts
    17
    Ahh ok.

    The reason there is a problem is when i'm trying to import the same data (have to use INSERT INTO and do it via import as well) when it comes to verifying the import parameters it is failing on "Verifying if the Date columns have date formats".

    I have tried everything, even going into the CSV and formatting the date so there is no time and it's being pulled through as "07-FEB-2010".
    Still fails.

    Any suggestions?

    Many thanks for your response!


    ***EDIT***

    Also if I export the table after the INSERT INTO to an xls file it still only brings the date through as "07-FEB-10" even though I am specifying that I want the year as "2010" and the HH24.MM.SS part is seemingly being ignored.... Any suggestions are more than welcome i'm starting to pull my hair out now...
    Last edited by u.c.dispatj; 06-09-11 at 08:44. Reason: Additional information

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by u.c.dispatj View Post
    The reason there is a problem is when i'm trying to import the same data (have to use INSERT INTO and do it via import as well) when it comes to verifying the import parameters it is failing on "Verifying if the Date columns have date formats".
    I fail so see how this is related to displaying data in a client application.

    I have tried everything, even going into the CSV and formatting the date so there is no time and it's being pulled through as "07-FEB-2010".
    Pulled by what?
    and what do you mean with "going into the CSV"? What SELECT statement are you using? Which application do you use to retrieve the data?

    Any suggestions?
    Again: it's the client application that formats the data. So check your application how you can adjust that format.

    Also if I export the table after the INSERT INTO to an xls file it still only brings the date through as "07-FEB-10" even though I am specifying that I want the year as "2010" and the HH24.MM.SS part is seemingly being ignored.... Any suggestions are more than welcome i'm starting to pull my hair out now...
    And how do you do that "export"? Which program are you using. That program is responsible for formatting the date.

  5. #5
    Join Date
    Jun 2011
    Posts
    17
    Quote Originally Posted by shammat View Post
    Pulled by what?
    and what do you mean with "going into the CSV"? What SELECT statement are you using? Which application do you use to retrieve the data?

    Again: it's the client application that formats the data. So check your application how you can adjust that format.

    And how do you do that "export"? Which program are you using. That program is responsible for formatting the date.

    1. Ok, I am exporting the data by going into the table and clicking EXPORT TABLE. Then it brings up a handy little wizard.

    2. Sorry by going into I mean manually opening and manually formatting the cells.

    3. The wizard in Oracle SQL developer.


    I am basically creating tables then using INSERT INTO to send data to the table.
    This is working fine except the date values. The field is declared as DATE and the TO_DATE should be converting to the correct format (dd-mm-yyyy hh24/mm/ss)

    I also want to be able to import the SAME data from a CSV.
    Need to be able to show I have done both basically.

    Thanks for the response.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by u.c.dispatj View Post
    I am basically creating tables then using INSERT INTO to send data to the table.
    This is working fine except the date values. The field is declared as DATE and the TO_DATE should be converting to the correct format (dd-mm-yyyy hh24/mm/ss)
    If those are the inserts from your initial post then the date value is inserted correctly.

    It's the display (in SQL Developer) that removes the time part.

    I don't use SQL Developer but I bet that there is some option to show the time part of a DATE column in there. Check the manual or post it to Oracle's SQL Developer forum.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
    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.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by u.c.dispatj View Post
    I am basically creating tables then using INSERT INTO to send data to the table.
    This is working fine except the date values. The field is declared as DATE and the TO_DATE should be converting to the correct format (dd-mm-yyyy hh24/mm/ss)
    If those are the inserts from your initial post then the date value is inserted correctly.

    It's the display (in SQL Developer) that removes the time part.

    I don't use SQL Developer but I bet that there is some option to show the time part of a DATE column in there. Check the manual or post it to Oracle's SQL Developer forum.

  9. #9
    Join Date
    Jun 2011
    Posts
    17
    Quote Originally Posted by anacedent View Post
    Alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
    anacedent, this worked perfectly!! Thankyou for your help I owe you one
    shammat, thanks for all your help!! Much appreciated
    Last edited by u.c.dispatj; 06-09-11 at 12:41.

Posting Permissions

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