3 Replies Latest reply: Jun 29, 2011 2:49 AM by jplogut-Oracle RSS

    Insert the time values in the column

    AnnPricks E
      Hi All,

      I was creating the table like

      create table ann(x number,y date);

      then, insert the values like

      insert into ann values(123,to_date(10:02:03,'HH24:MI:SS'));
      that means i want to insert time value in that 2nd column.

      Then, i put

      select * from ann;
      it display like

      x y

      123 1-JUN-11

      I was inserting only the time value,then Oracle defaultly took the 1st day of this month.. then i am using

      select to_char(y,'HH24:MI:SS') from ann;
      then i was getting the time value.........

      From this, My question is if i put select * from ann then it will display like
      x y

      123 00-00-00 (or) sysdate(28-JUN-11)

      Because i dont want date value in that column....i want only the time value......

      I dont want any NLS_DATE_FORMAT change.....

      Can anyone please share the answer for this?

      Edited by: 866916 on Jun 28, 2011 5:01 PM
        • 1. Re: Insert the time values in the column
          jplogut-Oracle
          Hello,
          I am not sure that it is related to Migration, but, anyway...

          With It is strongly discouraged to work with dates without specifying an explicit format...
          Internaly, dates are stored as number and always include both Date and Time.

          If you want to work with anything else than defaults (can be your defaults, your NLS defaults, or Oarcel defaults) you HAVE TO specify a format.

          In your case, when you insert a value to_date(10:02:03,'HH24:MI:SS'), you specify a specific format.
          Later, when you select * from ann, you do not give any format, so the date will be displayed with default formats (note the "s")

          Oracle choose to display 1-JUN-11 because :

          1. By default, Oracle displays only the date part, not the time part
          2. According to your NLS, the 6th month of the year spells JUN

          There are obviously a lot of other defaults that are taken in account...

          Obviously, if you specify the same format when selecting than the one you used when inserting, you will get the same value (was date, time, or both)

          The rule here is : "always specify an explicit format when dealing with DATE dataypes"

          Kind regards,
          Jean-Patrick
          • 2. Re: Insert the time values in the column
            AnnPricks E
            From this one you have to say like,

            we should use

            select to_char(y,'HH24:MI:SS') from ann;
            for getting the specific format output.... right?

            Is there any option to save only that time value not that date value(not internally also)?

            That means i want to save like

            00-00-00 10:02:03 in that time value ....
            here the date format like 00-00-00 and then required time format....

            if i am inserting the time value that stores like

            insert into ann values(to_date('10:02:03','HH24:MI:SS'))

            is there any option to store the data like 00-00-00 10:02:03 like this???????
            • 3. Re: Insert the time values in the column
              jplogut-Oracle
              Hello,
              Yes, using
              SQL> select to_char(y,'HH24:MI:SS') from ann;
              You will get :
              TO_CHAR(
              --------
              10:02:03

              but there is no option to save only that time value not that date value.

              However, you may use a "default date" you will always use when storing a time. 00-00-00 will be refused (00 is not a valid day), but you can use 01-01-01 :
              SQL> insert into ann values(123,to_date('01-01-01 10:02:03','DD-MM-YY HH24:MI:S
              S'));

              1 ligne created.

              SQL> select to_char(y,'HH24:MI:SS') from ann;

              TO_CHAR(
              --------
              10:02:03

              Also, to simplify, you may choose to use the first julian day as reference (with a J format) :

              Here is a sample :

              SQL> insert into ann values(123,to_date('1 10:02:03','J HH24:MI:SS'));

              1 ligne created.

              then,
              SQL> select to_char(y,'HH24:MI:SS') from ann;

              TO_CHAR(
              --------
              10:02:03


              Please refer to the SQL Language documentation at
              http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements001.htm#sthref154

              Kind regards,
              Jean-Patrick