RE: Date datatype

From: Aragon, Gabriel (GE Commercial Finance) <gabriel.aragon_at_ge.com>
Date: Tue, 5 Apr 2005 10:33:37 -0400
Message-ID: <84B673E70D068347A2F54A51457E5C27798E7B@CINMLVEM05.e2k.ad.ge.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]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

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 05 2005 - 10:35:49 CDT