OraFAQ Forum: SQL & PL/SQL » Query where condition has 4 rows in table but result is zero rows

Home » SQL & PL/SQL » SQL & PL/SQL » Query where condition has 4 rows in table but result is zero rows (Oracle 11)

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

Query where condition has 4 rows in table but result is zero rows [message #569566]

Mon, 29 October 2012 15:05

cplusplus1
Messages: 30
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.

Report message to a moderator

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

Michel Cadot
Messages: 54125
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

Report message to a moderator

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

cplusplus1
Messages: 30
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.

Report message to a moderator

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

Littlefoot
Messages: 16924
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.

Report message to a moderator

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

Michel Cadot
Messages: 54125
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 09:57:59 CDT 2013

Total time taken to generate the page: 0.09673 seconds