Home » SQL & PL/SQL » SQL & PL/SQL » date problem
date problem [message #185176] Mon, 31 July 2006 06:54 Go to next message
khosravi
Messages: 68
Registered: April 2006
Member
hello
please help me
verify below example
create table t3 (id number(5), dat date default sysdate);
table created;
insert into t3 values(1,date '2006-01-01');
1 row created.
insert into t3 values(2,date '2006-02-01');
1 row created.
insert into t3 (id) values(3);
1 row created.
insert into t3 (id) values(4);
1 row created.
insert into t3 (id) values(5);
1 row created.
commit;
alter session set nls_date_format='yyyy-mm-dd';
session altered.
select * from t3 ;
ID DAT
------ ---------------
1 2006-01-01
2 2006-02-01
3 2006-07-31
4 2006-07-31
5 2006-07-31
select * from t3 where dat='2006-07-31';
no rows selected .
select * from t3 where dat='2006-01-01';
ID DAT
------ ---------------
1 2006-01-01
please attention three rows exists that it's dat is 2006-07-31 but it's dat field values
retrieved by sysdate function and select * from t3 where dat='2006-07-31'
return no row
while select statement work fine against the rows that it's dat field values inserted directly by insert statement

do you know it's reason?
thanks
Re: date problem [message #185179 is a reply to message #185176] Mon, 31 July 2006 07:08 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SYSDATE contains time in it, not only date. Either use TRUNC on this value (such as TRUNC(SYSDATE) or TRUNC(date_column)), or include time portion into the SELECT statement.
Re: date problem [message #185182 is a reply to message #185176] Mon, 31 July 2006 07:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

SQL> select * from t3;

        ID DAT
---------- ----------
         1 2006-01-01
         2 2006-02-01
         3 2006-07-31
         4 2006-07-31
         5 2006-07-31

SQL>  select * from t3 where TRUNC(dat) ='2006-07-31'
  2  ;

        ID DAT
---------- ----------
         3 2006-07-31
         4 2006-07-31
         5 2006-07-31


OOPS ...... Beaten

Rajuvan.

[Updated on: Mon, 31 July 2006 07:20]

Report message to a moderator

Re: date problem [message #185189 is a reply to message #185176] Mon, 31 July 2006 07:58 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
khosravi wrote on Mon, 31 July 2006 07:54



select * from t3 where dat='2006-07-31';
no rows selected .



In addition to the other answers, why would you compare a date column to a character string?

[Updated on: Mon, 31 July 2006 07:58]

Report message to a moderator

Previous Topic: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Next Topic: Change of column positions..
Goto Forum:
  


Current Time: Fri Dec 02 14:14:06 CST 2016

Total time taken to generate the page: 0.08476 seconds