Home » SQL & PL/SQL » SQL & PL/SQL » Bug while using Between for Date Fields (Oracle9i Enterprise Edition Release 9.2.0.1.0)
icon5.gif  Bug while using Between for Date Fields [message #325362] Fri, 06 June 2008 00:34 Go to next message
Preeti_Mathur
Messages: 4
Registered: June 2008
Junior Member
I am creating an application, where Oracle 9i is been used as the backend, and the fornt end is VB6.0
I had VB Service Pack installed at my system.
Now when i execute the following query from SQL Plus, i get the correct O/P, as my requirement, but when i execute the same query from the application frontend then the desired records are not received there, that is the RecordSet is not been populated there.
I had used ADODB Connection and Recordset to execute the query.

The query is:
"Select BirthDate from EmpMaster where BirthDate between '01-Jan-08' and '31-Jan-08'"

I had also installed the providers for Oracle9i on my system.

Any help will be highly appreciated.
Thanking You all in advance.
Re: Bug while using Between for Date Fields [message #325364 is a reply to message #325362] Fri, 06 June 2008 00:43 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
try using the complete command.

use TO_DATE and try again. This might work.
Re: Bug while using Between for Date Fields [message #325366 is a reply to message #325362] Fri, 06 June 2008 00:45 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member

The query is:
"Select BirthDate from EmpMaster where BirthDate between '01-Jan-08' and '31-Jan-08'"

With Oracle characters between single quote marks are STRINGS
Re: Bug while using Between for Date Fields [message #325370 is a reply to message #325366] Fri, 06 June 2008 00:48 Go to previous messageGo to next message
Preeti_Mathur
Messages: 4
Registered: June 2008
Junior Member
Error at line 1:
ORA-00904: "JAN": invalid identifier

It reports this error if i remove the single quotes from the query.
Sad
Re: Bug while using Between for Date Fields [message #325373 is a reply to message #325364] Fri, 06 June 2008 00:50 Go to previous messageGo to next message
Preeti_Mathur
Messages: 4
Registered: June 2008
Junior Member
U mean i should write the query like this:

"Select BirthDate from EmpMaster where BirthDate between to_date('01-Jan-08') and to_date('31-Jan-08');"
Re: Bug while using Between for Date Fields [message #325374 is a reply to message #325370] Fri, 06 June 2008 00:52 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Preeti_Mathur wrote on Thu, 05 June 2008 22:48
Error at line 1:
ORA-00904: "JAN": invalid identifier

It reports this error if i remove the single quotes from the query.
Sad

HUH?
what are you doing & why are you upset?
Re: Bug while using Between for Date Fields [message #325375 is a reply to message #325373] Fri, 06 June 2008 00:52 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
SELECT BirthDate 
FROM EmpMaster 
WHERE BirthDate BETWEEN TO_DATE('01-Jan-08','DD-Mon-YY') 
                AND TO_DATE('31-Jan-08','DD-Mon-YY')
Re: Bug while using Between for Date Fields [message #325377 is a reply to message #325375] Fri, 06 June 2008 00:58 Go to previous message
Preeti_Mathur
Messages: 4
Registered: June 2008
Junior Member
Thanks a lot, its done... Smile
Cheers...
Previous Topic: validating mail id
Next Topic: EXTRACT DATA
Goto Forum:
  


Current Time: Thu Dec 08 16:32:26 CST 2016

Total time taken to generate the page: 0.10962 seconds