Pblm while inserting Date

[sujitopics]

Hai friends
i created table in oracle 8i with date field like this.
create table datetest (d date);
but i am not able to insert date into that table.
i used this query.
insert into datetest values ("1999-12-12&quot
but it is saying column not allowed here error.
how can i insert data with insert query.
please help me.
can i insert data and time into the field ?
Thanks in advance
Yours
Suji

 

[sfvb]

Suji,

Try using single quotes. Also, you may need to alter the statement to use the DD-MON-YYYY format, as in:
insert into datetest values ('12-DEC-1999');

 

[mesuj]

to use your own date format use

insert into datetest values ( to_date('1999-12-12','YYYY-MM-DD') )

 

[sujitopics]

Dear Mesuj

I just tried with ur Query
insert into datetest values ( to_date('1999-12-12','YYYY-MM-DD') )
This is working fine.
Thankyou VeryMuch

In the same way can i insert hh:mm:sec also like this ?
insert into datetest values ( to_date('1999-12-12 01:01:02','YYYY-MM-DD hh:mm:sc') )
when i try with the above query [with hours min and sec] it is showing error.

is it possible to insert time also along with data in oracle.

Thanks in advance
Yours
Suji

 

[stefanhei]

Hi.
Formatstring for minutes is MI and for seconds SS:

insert into datetest values ( to_date('1999-12-12 01:01:02','YYYY-MM-DD hh:MI:SS') )

Also keep in mind that you specify 12-hour day when you use hh.
Either use HH24 or add AM (or PM) to your string.

Stefan

 

[sujitopics]

Dear Stefan
Thankyou VeryMuch
insert into datetest values ( to_date('1999-12-12 01:01:02','YYYY-MM-DD hh24:MI:SS') )
with the above query it is inserting into oracle database.
it is fine.
but when i am trying to fetch the records from table using select query it is showing only date
it is not showing time with select query.
SQL> select * from datetest;
--------
12-DEC-99
12-DEC-99
12-DEC-99
can we see the time also ?
is it possible to view the time, date with sql query.
thanks in advance
yours
Suji

 

[karluk]

The reason that you are not seeing hour, minutes, and seconds is that Oracle is using its default date format. You need to specify the format you want, just as you did on the insert statement:

select to_char(d,'YYYY-MM-DD hh24:MI:SS') from datetest;

There are numerous other date formats. You can pick the one you like. See the SQL Reference in your Oracle documentation for details.

 

[stefanhei]

You can also set the format for your session with:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
Then all dates are shown in the given format.

Stefan

 

[sujitopics]

Hai Friends
Thankyou VeryMuch
select to_char(d,'YYYY-MM-DD hh24:MI:SS') from datetest;
The above query is working Fine. and displaying with time also.

In Oracle can we show Milliseconds along with date and time ?
Just now i searched in oracle documentation.
and i didnot found the reference to represent milliseconds.
is it possiblt to represent the milli seconds in oracle ?
if anybody knows please tell me.
i need to represent milliseconds also in my application.

Thanks in advance
Yours
Suji

 

[carp]

Sorry - the Oracle date type only goes down to a resolution of seconds.