RE: Date datatype
- From: "Aragon, Gabriel (GE Commercial Finance)" <gabriel.aragon@xxxxxx>
- To: <anwarsk@xxxxxxxxx>, "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- Date: Tue, 5 Apr 2005 10:33:37 -0400
According to the FM, the function doesn't work with date field, only =
with timestamp with time zone data type:
sqlplus> create table t1 (d date, tz timestamp with time zone);
Table created.
sqlplus> insert into t1 values (sysdate, sysdate);
1 row created.
sqlplus> select extract(minute from d) from t1;
select extract(minute from d) from t1
*
ERROR at line 1:
ORA-30076: invalid extract field for extract source
sqlplus> select extract(minute from tz) from t1;
EXTRACT(MINUTEFROMTZ)
---------------------
19
but with date part of the field, there is no problem:
sqlplus> select extract(year from d) from t1;
EXTRACT(YEARFROMD)
------------------
2005
sqlplus> select extract(year from tz) from t1;
EXTRACT(YEARFROMTZ)
-------------------
2005
Gabriel
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Anwar Khudhair
Sent: Martes, 05 de Abril de 2005 12:53 p.m.
To: Christian Antognini
Cc: oracle-l
Subject: Re: Date datatype
Hi,
> 2.. I'm trying the EXTRACT function. Year, month and
> day worked fine but not the hour, minute and
> second. How can I do it?
It should work. What does it mean "... but not..."?=20
If you copy/paste an example it's easier to help you...
To extract the year: Select extract(year from Date_Column) from =
My_table.
To extract the hour: Select extract(hour from Date_Column) from =
My_table. May be wrong sentance?
> 3.. I tried to add a numeric value from another
> field to a date column and it worked for the
> date. Is it possible to do the same thing with the time?
Yes. E.g. add 1/24 to add one hour... i.e. you should do some math...
As you said 'If you copy/paste an example it's easier to help' me.
Thanks.
HTH
Chris
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
