OraFAQ Forum: SQL & PL/SQL » Problems with dates

Home » SQL & PL/SQL » SQL & PL/SQL » Problems with dates

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problems with dates [message #4387]

Wed, 04 December 2002 09:36

Raquel Cascales
Messages: 3
Registered: December 2002

Junior Member

I have the following table:

name date
a 1/1/2002
b 12/12/2002
raquel 12/12/2002

I'm doing the query:

select name
from table
where name='raquel'
and date='12/12/2002';

The result is: there's any row selected.
Can you help me?
Thank you.

Report message to a moderator

Re: Problems with dates [message #4392 is a reply to message #4387]

Wed, 04 December 2002 11:34

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Read up a bit on the default date format using NLS_DATE_FORMAT. But you should never rely on implicit date conversions - always handle them explicitly:

select name
  from table
 where name = 'raquel'
   and date = to_date('12/12/2002', 'mm/dd/yyyy');

Report message to a moderator

Re: Problems with dates [message #4402 is a reply to message #4392]

Thu, 05 December 2002 01:38

Raquel Cascales
Messages: 3
Registered: December 2002

Junior Member

We have tried this also and the answer is always "no rows selected".

Report message to a moderator

Re: Problems with dates [message #4410 is a reply to message #4402]

Thu, 05 December 2002 10:34

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

I would guess then that you have stored a time component in your DATE column. If this query returns a count greater than 0, than this is the case:

select count(*)
  from table
 where date_column <> trunc(date_column);

If so, then you need to use TRUNC on the filter:

where trunc(date_column) = to_date('12/05/2002', 'mm/dd/yyyy')

Report message to a moderator

Re: Problems with dates [message #4412 is a reply to message #4410]

Thu, 05 December 2002 10:43

Raquel Cascales
Messages: 3
Registered: December 2002

Junior Member

Yes, you were right! Thank you very much.

Raquel.

Report message to a moderator

Previous Topic:	Copy from -- append
Next Topic:	Truncate spaces in output

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 12:01:08 CDT 2024