Home » SQL & PL/SQL » SQL & PL/SQL » Date search in sql (Oracle 9.2.0.3)
Date search in sql [message #445026] Thu, 25 February 2010 05:19 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

DT1 is a column of date datatype and there is no index created on the table
I want to add the below lines in the sql
TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'

Which one should I use in where condition to query and why? please advice:

1. TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'
2. DT1 BETWEEN '01/01/2005' AND '31/12/2009' (as NLS date format will not change)
3. DT1 BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND ('31/12/2009' ,'dd/mm/yyyy')
Re: Date search in sql [message #445028 is a reply to message #445026] Thu, 25 February 2010 05:23 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
3

Even if there isn't an index now you might end up adding one and don't say the nls format will never change as there is no way you can guarantee that.
Re: Date search in sql [message #445029 is a reply to message #445026] Thu, 25 February 2010 05:26 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually no.

4 - DT1 BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND ('31/12/2009 23:59:59' ,'dd/mm/yyyy hh24:mi:ss')

otherwise you'll end up ignoring anything on the 31st dec where the time component is after midnight.

Re: Date search in sql [message #445030 is a reply to message #445029] Thu, 25 February 2010 05:28 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
did not understand the last line

"there is no way you can guarantee that"--my client says that its never gonna be changed. Razz

[Updated on: Thu, 25 February 2010 05:29]

Report message to a moderator

Re: Date search in sql [message #445032 is a reply to message #445026] Thu, 25 February 2010 05:38 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
They might change their mind. And if they do you're going to have to change a lot of code if you used option b.

Besides nls formats are inherited from the client machines and they can be changed. Never rely on nls format being consistent.
Re: Date search in sql [message #445033 is a reply to message #445029] Thu, 25 February 2010 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or 5:

    DT1 >= to_date('01/01/2005','dd/mm/yyyy') 
AND DT1 < to_date('01/01/2010','dd/mm/yyyy') 

Regards
Michel

[Updated on: Thu, 25 February 2010 05:39]

Report message to a moderator

Re: Date search in sql [message #445050 is a reply to message #445033] Thu, 25 February 2010 07:55 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
there is no end for these "Or 5..." statements.
Laughing
Previous Topic: Trigger problem
Next Topic: Taking months off sysdate
Goto Forum:
  


Current Time: Sat Oct 01 09:14:51 CDT 2016

Total time taken to generate the page: 0.04104 seconds