This discussion is archived
3 Replies Latest reply: Jun 29, 2011 12:49 AM by jplogut RSS

Insert the time values in the column

AnnPricks E Guru
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points