OraFAQ Forum: SQL & PL/SQL » TO_CHAR( date) in WHERE clause

Home » SQL & PL/SQL » SQL & PL/SQL » TO_CHAR( date) in WHERE clause

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

TO_CHAR( date) in WHERE clause [message #167136]

Tue, 11 April 2006 08:37

SaraC
Messages: 81
Registered: August 2005

Member

Does anyone know can you use a date format inside the WHERE clause

e.g

WHERE TO_CHAR(datetime,'DD-MON-YYYY HH24:MI:SS) <= otherdatetime

looks like valid syntax but returns the ORA-01830: date format picture ends before converting entire input string error.

Thx

Report message to a moderator

Re: TO_CHAR( date) in WHERE clause [message #167138 is a reply to message #167136]

Tue, 11 April 2006 08:41

aciolac
Messages: 242
Registered: February 2006

Senior Member

1) Is there are no problem that You are not put an asterisk(red bold)?
WHERE TO_CHAR(datetime,'DD-MON-YYYY HH24:MI:SS') <= otherdatetime
2)Why You need to compare char variable and date variable???? Is it more simple that to compare two date variables?

Report message to a moderator

Re: TO_CHAR( date) in WHERE clause [message #167139 is a reply to message #167136]

Tue, 11 April 2006 08:47

SaraC
Messages: 81
Registered: August 2005

Member

Sorry missed the closing quote on the date format when I printed it here - not like that in my actual query.

Have tried converting first to DATE so that comparing DATe and Date
Think it works ok just have to prove it will work every time

Thanks

Report message to a moderator

Re: TO_CHAR( date) in WHERE clause [message #167184 is a reply to message #167136]

Tue, 11 April 2006 12:32

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

SaraC wrote on Tue, 11 April 2006 09:37

This is an utterly ridiculous thing to do. In your case April 11, 2006 will be less than January 12, 1985.

Yes, 11-APR-2006 is less than 12-JAN-1985 when you compare character strings.

SQL> l
  1  select 1 from dual
  2* where to_char(sysdate,'dd-mon-yyyy') < '12-JAN-1985'
SQL> /

         1
----------
         1

How is this an expert question?

[Updated on: Tue, 11 April 2006 12:40]

Report message to a moderator

Re: TO_CHAR( date) in WHERE clause [message #167290 is a reply to message #167136]

Wed, 12 April 2006 05:00

rohank009
Messages: 9
Registered: April 2006
Location: India

Junior Member

Hi,

u can try this out.

SELECT 1 FROM dual
WHERE TO_DATE(TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') > '12-jan-1960'

Regards,
Rohan

Report message to a moderator

Re: TO_CHAR( date) in WHERE clause [message #167294 is a reply to message #167290]

Wed, 12 April 2006 05:22

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Nonono: compare DATEs to DATEs, Rohan. Don't transform them to strings.

[edit]

rohank009 wrote

u can try this out.

U is not a member of this forum, so I doubt whether he/she can try it out. You probably meant 'you can try this out.'

MHE

[Updated on: Wed, 12 April 2006 05:24]

Report message to a moderator

Re: TO_CHAR( date) in WHERE clause [message #167297 is a reply to message #167136]

Wed, 12 April 2006 05:46

rohank009
Messages: 9
Registered: April 2006
Location: India

Junior Member

Hi,

I think trying just a select statement won't do any harm to the database,
and i agree with u regarding date comparison,
but i just wanted to give the answer to the question asked.

Report message to a moderator

Previous Topic:	ALTER VIEW
Next Topic:	group by Hour range

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 26 04:53:42 CDT 2025