Re: ORA-01821: date format not recognized [TO_DATE]
- From: Mihajlo Tekic <mihajlo.tekic@xxxxxxxxx>
- To: aluoor@xxxxxxxxx
- Date: Wed, 27 Oct 2010 14:18:18 -0500
I think fractional seconds are supported withTIMESTAMP, but not with DATE
datatype.
You should use TO_TIMESTAMP instead.
Useful docs:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i1847
Example:
SQL> select
to_timestamp('2010/10/27-07:33:04.229','YYYY/MM/DD-HH24:MI:SS.FF') as
p_timestamp from dual;
P_TIMESTAMP
---------------------------------------------------------------------------
27-OCT-10 07.33.04.229000000 AM
~Mihajlo
On Wed, Oct 27, 2010 at 2:02 PM, Sven Aluoor <aluoor@xxxxxxxxx> wrote:
> Hi folks
>
> I wish to have all records where the "time_created" is lesser than
> current date minus one hour.
>
> SELECT *
> FROM trans
> WHERE status = '0'
> AND TO_DATE (time_created, 'YYYY/MM/DD-HH24:MI:SS.FF') < SYSDATE - 1 /
> 24
>
> time_created is a "VARCHAR2(23 Byte)" with the following contents:
>
> 2010/10/27-07:33:04.229
> 2010/10/27-07:33:03.135
> 2010/10/21-12:43:18.371
>
> error: ORA-01821: date format not recognized
>
> cheers Sven
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
