Home » SQL & PL/SQL » SQL & PL/SQL » date datatype
date datatype [message #190270] Tue, 29 August 2006 23:45 Go to next message
charankarthikeyan
Messages: 16
Registered: August 2006
Junior Member
Hi,

how can we insert date along with time in date datatype

ex: create table t1(date_time date);

then how to insert date & time.

whether the query select sysdate from dual will fetch only date or with time.

pls help
Re: date datatype [message #190281 is a reply to message #190270] Wed, 30 August 2006 00:34 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


you don't have to worry about that ,coz sysdate has a time part in it.
create table test
(
t date
)
/
insert into test values(sysdate);
/
select to_char(t,'dd-mm-yy hh24:mi:ss')t from test



regards,
Re: date datatype [message #190296 is a reply to message #190270] Wed, 30 August 2006 02:07 Go to previous messageGo to next message
charankarthikeyan
Messages: 16
Registered: August 2006
Junior Member
ok well,

the query 'insert into test values(sysdate);'

1.instead of sysdate if i update '30-aug-04', from where oracle
will take the time input, whether it takes the current time while inserting the row

2.bcos i want the exact time for ex:
the DOB of a child with the exact time of born should be feeded in a table in this scenario ???

pls assist
Re: date datatype [message #190307 is a reply to message #190296] Wed, 30 August 2006 02:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you do
INSERT INTO test VALUES ('30-aug-04');
then you are making several mistakes which you'd do well to get out of the habit of quickly.

1) Implicit conversions. You are inserting into a DATE column, therefore you need to use a date. In order to get a date, oracle implicitly rewrites '30-aug-04' as TO_DATE('30-aug-04'), which will attempt to use the default date format mask to convert the provided string into a date. You should always use an explicit TO_DATE (to avoid type conversions happening that you are not aware of) and you should always explicitly state the format mask that is to be used (to ensure that your code will work on someone elses machinve where the NLS_DATE_FORMAT may be different.

2) Y2K. We went through this in 1997-99. It takes 4 digits to specify a year.

3) Specifying column lists. It's much better to tell the insert statement which columns you are inserting into. That way if the table definition changes, your code still works.

So, this query should be written:
INSERT INTO test (date_time) VALUES (to_date('30-aug-2004','dd-mon-yyyy'));


Now, back to the question.

The date that you have supplied has no time component to it, so Oracle will use the default time, which is midnight.

If you do:
SELECT to_char(date_time,'dd-mon-yyyy hh24:mi:ss') FROM test;
you will see this.

All you need to do is to use a date format which includes a time element.
eg
INSERT INTO test (date_time) VALUES (to_date('30-aug-2004 13:50:01','dd-mon-yyyy hh24:mi:ss'));
Re: date datatype [message #190317 is a reply to message #190270] Wed, 30 August 2006 03:27 Go to previous messageGo to next message
charankarthikeyan
Messages: 16
Registered: August 2006
Junior Member
well,

indeed thanks for your so much support in explaining in detail
then correct me if i'm wrong
1.TO_DATE function converts only data types which is of char & number to date datatype isn'it then in the table, the datatype of date_time is date only, then how the to_date function converts date_time(which is of date datatype) & again to_date(of date datatype) in the insert statement ,, assistance pls
Re: date datatype [message #190319 is a reply to message #190317] Wed, 30 August 2006 03:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid I don't entirely understand your question.

The column DATE_TIME is of type DATE, which in Oracle includes both a date and a time element.

When inserting into table TEST, you need to insert a field of type DATE.
The easiest way to get one of these is to perform an explicit to_date on a varchar2 string.
Re: date datatype [message #190405 is a reply to message #190319] Wed, 30 August 2006 09:26 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
It is important to remember that, in Oracle, a field of type DATE contains a number that Oracle translates into a date & time. The number is a decimal number with the date portion before the decimal and the time portion after the decimal.

When you save a date from someone's input, you have to take the string representation of the date (and time) and translate it into the numerical date format. This is done by the to_date function...

insert into (birthday) 
  values 
(to_date('10/23/2006','MM/DD/RRRR'));

insert into (birthday)
  values
(to_date('06-AUG-2005 14:32:21','DD-MON-YYYY HH24:MI:SS'))


The first format saves the date and sets the time to 00:00:00. So, using to_char(birthday,'DD-MON-YYYY HH24:MI:SS') would result in the character string "23-OCT-2006 00:00:00" for the first insert statement and "06-AUG-2005 14:32:21" for the second insert statement.

To retrieve this information from the table and display it in a format you like, use the to_char function...

select name, birthday
into  vName, vBirthday
from   registration;

htp.p('Hello '||vName||', your birthday is on '||to_char(vBirthday, 'MM/DD/YYYY');



When you compare two dates, make sure you're comparing the numerical date and not the character string...

if to_date('03/24/2005','MM/DD/YYYY') < sysdate then
  ...
end if;


Biggest thing to remember about dates, if you're comparing dates, use to_date to make sure all comparisons are done as DATE type, not strings (comparing sysdate to '30-AUG-06' won't work!)

When displaying dates, use to_char and format your date to the output you desire.
Re: date datatype [message #190465 is a reply to message #190270] Wed, 30 August 2006 22:43 Go to previous message
charankarthikeyan
Messages: 16
Registered: August 2006
Junior Member
thanks JROW & RONALD,

for your valuable contribution indeed it helped me a lot in understanding date.

karthik
Previous Topic: ora_sql_txt
Next Topic: a select statemet question ??
Goto Forum:
  


Current Time: Sun Dec 11 04:28:50 CST 2016

Total time taken to generate the page: 0.20284 seconds