ORA-01821: date format not recognized [TO_DATE]

[Sven Aluoor]

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
--
http://www.freelists.org/webpage/oracle-l

[Sweetser, Joe]

Maybe use to_timestamp, instead?

-joe

SQL> create table joe (time_created varchar2(23 byte));

Table created.

SQL> insert into joe values ('2010/10/27-07:33:04.229');

1 row created.


SQL> select to_date(time_created, 'YYYY/MM/DD-HH24:MI:SS.FF') from joe;
select to_date(time_created, 'YYYY/MM/DD-HH24:MI:SS.FF') from joe
*
ERROR at line 1:
ORA-01821: date format not recognized


SQL> c/date/timestamp
1* select to_timestamp(time_created, 'YYYY/MM/DD-HH24:MI:SS.FF') from
joe
SQL> /

TO_TIMESTAMP(TIME_CREATED,'YYYY/MM/DD-HH24:MI:SS.FF')
------------------------------------------------------------------------
---
27-OCT-10 07.33.04.229000000 AM

SQL>



-----Original Message-----
From: oracle-l-bounce-***@public.gmane.org
[mailto:oracle-l-bounce-***@public.gmane.org] On Behalf Of Sven Aluoor
Sent: Wednesday, October 27, 2010 1:02 PM
To: oracle-l-***@public.gmane.org
Subject: ORA-01821: date format not recognized [TO_DATE]

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
--
http://www.freelists.org/webpage/oracle-l




Confidentiality Note: This message contains information that may be confidential and/or privileged. If you are not the intended recipient, you should not use, copy, disclose, distribute or take any action based on this message. If you have received this message in error, please advise the sender immediately by reply email and delete this message. Although ICAT Managers, LLC, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. Thank you.


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

[Mihajlo Tekic]

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

Post by Sven Aluoor
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
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
--
http://www.freelists.org/webpage/oracle-l

[Sven Aluoor]

Thanks to Joe Sweetser and Mihajlo Tekic. With "TO_TIMESTAMP" it works.

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