Home » SQL & PL/SQL » SQL & PL/SQL » Query where condition has 4 rows in table but result is zero rows (Oracle 11)
Query where condition has 4 rows in table but result is zero rows [message #569566] Mon, 29 October 2012 15:05 Go to next message
cplusplus1
Messages: 40
Registered: October 2012
Location: usa
Member
There are 4 rows in table with stat_flag 'Updated Record' and stat_date with todays date.

stat date has date & time both, for that reason just trying to format with yyyy.mm.dd
I am getting zero rows as result.

where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')

Thanks a lot for the helpful info.
Re: Query where condition has 4 rows in table but result is zero rows [message #569567 is a reply to message #569566] Mon, 29 October 2012 15:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TO_DATE on a date is a misuse.
Use TRUNC instead.

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Query where condition has 4 rows in table but result is zero rows [message #569568 is a reply to message #569567] Mon, 29 October 2012 16:13 Go to previous messageGo to next message
cplusplus1
Messages: 40
Registered: October 2012
Location: usa
Member
Hello Michel,

All i am doing is:

select * from tab_DMStat where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')

can you please kindly help with trunc.

Re: Query where condition has 4 rows in table but result is zero rows [message #569569 is a reply to message #569568] Mon, 29 October 2012 16:28 Go to previous messageGo to next message
Littlefoot
Messages: 19540
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SYSDATE is a function that returns a DATE, so - as Michel said - you don't TO_DATE a DATE.

select *
from tab_dmstat
where stat_flag = 'Updated Record'
  and trunc(stat_date) = trunc(sysdate)
is what you are after, probably.
Re: Query where condition has 4 rows in table but result is zero rows [message #569584 is a reply to message #569568] Tue, 30 October 2012 00:48 Go to previous message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cplusplus1 wrote on Mon, 29 October 2012 22:13
Hello Michel,

All i am doing is:

select * from tab_DMStat where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')

can you please kindly help with trunc.


Michel Cadot wrote on Mon, 29 October 2012 21:17
...
...Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals....


All functions are described in Database SQL Reference, please refer to it when we point you to a function instead of waiting for being spoonfed.

Regards
Michel

Previous Topic: T-Sql to Pl/Sql
Next Topic: why did not "no_merge" work?
Goto Forum:
  


Current Time: Tue Sep 02 23:36:30 CDT 2014

Total time taken to generate the page: 0.13502 seconds