Home » SQL & PL/SQL » SQL & PL/SQL » to_date problem
to_date problem [message #188601] Sun, 20 August 2006 13:27 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hello you all,

I have this to_date problem :


v_date := to_date('31-12-2299 9:00:00','dd-mm-yyyy HH24:mi:ss');

UPDATE my_table a 
  SET a.my_date = SYSDATE
 WHERE a.my_date = v_date;



my_date field is filled with the date : '31-12-2299
but, It seems to compare 31-12-99 with 31-12-2299
And this is ofcourse false.

How can I solve this problem?

Anyone ideas?

Thanks in advance
Re: to_date problem [message #188611 is a reply to message #188601] Sun, 20 August 2006 23:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is the datatype of my_date DATE or VARCHAR2?
If it is date, do a
select to_char(my_date, 'dd-mm-yyyy hh24:mi:ss')
from   my_table

to see the exact value of my_date.

[Edit: typo]

[Updated on: Sun, 20 August 2006 23:09]

Report message to a moderator

Re: to_date problem [message #188801 is a reply to message #188601] Mon, 21 August 2006 13:47 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Also, you can do your repair without setting up a variable.

UPDATE my_table a
SET a.my_date = SYSDATE
WHERE a.my_date = to_date('31-12-2299 9:00:00','dd-mm-yyyy HH24:mi:ss');
Previous Topic: displaying date in reports using SQL*PLUS
Next Topic: SQL capture the output statement
Goto Forum:
  


Current Time: Sun Dec 11 06:14:22 CST 2016

Total time taken to generate the page: 0.04710 seconds