| Oracle 9i Query Issue [message #527185] |
Sun, 16 October 2011 11:42  |
mrnaveed
Messages: 74
Registered: December 2009
Location: Pakistan
|
Member |
|
|
Dear All,
i have problem i am making a project in VB.NET the query is not working then i copied the query and tun it on SQL Plus but it gives me zero counts please see the query bellow
SQL> SELECT COUNT(*) FROM WO WHERE TO_CHAR(SCHEDULED_DATE,'DD/MON/YYYY') = TO_CHAR('DD/MON/YYYY');
COUNT(*)
----------
0
Please help me
|
|
|
|
|
|
|
|
|
|
| Re: Oracle 9i Query Issue [message #527189 is a reply to message #527188] |
Sun, 16 October 2011 12:05  |
mrnaveed
Messages: 74
Registered: December 2009
Location: Pakistan
|
Member |
|
|
Oh sorry I am using this query in VB.NET but it is not returning any result when i copy this query and run on SQL Plus it produces COUNT o but it should fetch the date
here is my actual query
com = New OleDbCommand("SELECT COUNT(*) FROM WO WHERE TO_CHAR(SCHEDULED_DATE, 'DD/MM/YYYY') = '" & checkDate.ToString("DD/MM/YYYY") & "'", con)
|
|
|
|
|
|
| Re: Oracle 9i Query Issue [message #527313 is a reply to message #527186] |
Mon, 17 October 2011 08:10 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
BlackSwan wrote on Sun, 16 October 2011 12:45It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
>WHERE TO_CHAR(SCHEDULED_DATE,'DD/MON/YYYY') = TO_CHAR('DD/MON/YYYY');
above will NEVER be TRUE
As Mr. Swan said, this will never be true but I tend to think for a different reason. For example, use today's date. You are dividing 17 by OCT. I personally do not know what the result would be to that. Then you are dividing that result by 2011, an equally mysterious result.
TO_CHAR is used to make string values of numbers or DATEs.
|
|
|
|
| Re: Oracle 9i Query Issue [message #527316 is a reply to message #527313] |
Mon, 17 October 2011 08:30 |
cookiemonster
Messages: 13968
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I don't think that code does what you think it does Joy:
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
SQL> SELECT TO_CHAR('DD/MON/YYYY') FROM dual;
TO_CHAR('DD
-----------
DD/MON/YYYY
SQL>
There's no division going on.
|
|
|
|
| Re: Oracle 9i Query Issue [message #527341 is a reply to message #527316] |
Mon, 17 October 2011 12:42 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
cookiemonster wrote on Mon, 17 October 2011 09:30I don't think that code does what you think it does Joy:
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
SQL> SELECT TO_CHAR('DD/MON/YYYY') FROM dual;
TO_CHAR('DD
-----------
DD/MON/YYYY
SQL>
There's no division going on.
Oh joy.
I suspect that the time portion of SCHEDULED_DATE is causing zero rows to be returned.
[Updated on: Mon, 17 October 2011 12:43] Report message to a moderator |
|
|
|
|
|
| Re: Oracle 9i Query Issue [message #527349 is a reply to message #527341] |
Mon, 17 October 2011 13:40 |
cookiemonster
Messages: 13968
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
joy_division wrote on Mon, 17 October 2011 18:42
I suspect that the time portion of SCHEDULED_DATE is causing zero rows to be returned.
I suspect you need some coffee. That select didn't return a date.
Blackswan's explanation is the correct one.
Would it help if I pointed out that it's this version of to_char that's getting used.
|
|
|
|
| Re: Oracle 9i Query Issue [message #527364 is a reply to message #527349] |
Mon, 17 October 2011 15:19 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
And to use my favorite quote that I made up:
Holy crapenstein!
I don't know how I didn't see that. I am ashamed, or I have hit the age where it is all downhill now and better take advantage of memory while I still have it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
