RE: Sql Oddity
Date: Mon, 18 Jan 2010 16:03:59 -0500
Thank you all, that seems to have done it. If I set the date back more that 0.125 from the current sysdate it works as advertised. Therefore it has to have something to do with the timezone.
Senior Oracle DBA/NA Team Lead
From: Stephane Faroult [mailto:sfaroult_at_roughsea.com] Sent: Monday, January 18, 2010 3:52 PM
Cc: oracle-l_at_freelists.org; Goulet, Richard Subject: Re: Sql Oddity
Exactly my thought - I was just checking the docs when Ilmar answered, SYSTIMESTAMP returns a TIMESTAMP WITH TIMEZONE, not a TIMESTAMP.
Ilmar Kerm wrote:
> On Mon, Jan 18, 2010 at 10:26 PM, Goulet, Richard
> <Richard.Goulet_at_parexel.com <mailto:Richard.Goulet_at_parexel.com>>
> Anyone ever seen this: A developer executes the following
> code in an Oracle 9i database (220.127.116.11)
> create table test_date_drop_me (test_column timestamp default
> insert into test_date_drop_me values (sysdate);
> select CASE WHEN systimestamp> t.test_column THEN 1 ELSE 0 END
> from test_date_drop_me t;
> He gets a 0 each time the final select statement is excecuted no
> matter what the value in the table. In a 10.2.0.4 and 18.104.22.168
> database we get a 1. Any ideas???
> Could it be related to different time zones for the client and server?
> Ilmar Kerm
> Sent from Tallinn, Harjumaa