OraFAQ Forum: SQL & PL/SQL » Select clause issue

Home » SQL & PL/SQL » SQL & PL/SQL » Select clause issue

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

Select clause issue [message #604080]

Mon, 23 December 2013 22:45

vssunil86
Messages: 3
Registered: December 2013
Location: Chennai

Junior Member

Hi All,

I am trying to get the list of weekends in a month by using below query:

select to_char(sysdate-level ,'Day') as DayOfWeek,to_char(trunc(sysdate-level)) as DateofMonth from dual
where to_char(sysdate-level, 'D') in ('7','1') and (to_char(sysdate,'mm')= to_char(sysdate-level,'mm'))
connect by level <= 31
union
select to_char(sysdate+level ,'Day') as DayOfWeek,to_char(trunc(sysdate+level)) as DateofMonth from dual
where to_char(sysdate+level, 'D') in ('7','1') and (to_char(sysdate,'mm')= to_char(sysdate+level,'mm'))
connect by level <= 31
order by DateofMonth

However, when I try to use the below it gives me an empty result:

[replace to_char(sysdate-level, 'D') in ('7','1') with the below]

to_char(sysdate-level ,'Day') in ('Saturday','Sunday')

I am not sure is it a syntax/logical error...

Can someone please help...

Thanks

Report message to a moderator

Re: Select clause issue [message #604081 is a reply to message #604080]

Mon, 23 December 2013 22:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Clues below should solve your mystery.

SQL> select length(to_char(sysdate,'Day')) , to_char(sysdate,'Day') from dual;

LENGTH(TO_CHAR(SYSDATE,'DAY')) TO_CHAR(SYSDATE,'DAY')
------------------------------ ------------------------------------
                             9 Monday

Report message to a moderator

Re: Select clause issue [message #604084 is a reply to message #604081]

Mon, 23 December 2013 23:09

vssunil86
Messages: 3
Registered: December 2013
Location: Chennai

Junior Member

Thanks BlackSwan... I got the issue.... it was due to space; I did not guessed it was due to space issue; thanks for your quick advise.

select to_char(sysdate-level,'D') as DayCount,to_char(sysdate-level ,'Day') as DayOfWeek,to_char(trunc(sysdate-level)) as DateofMonth
from dual connect by level <= 31

select to_char(sysdate-level,'D') as DayCount,to_char(sysdate-level ,'Day') as DayOfWeek,to_char(trunc(sysdate-level)) as DateofMonth
from dual where to_char(sysdate-level, 'D') in ('7','1') connect by level <= 31

select to_char(sysdate-level,'D') as DayCount,to_char(sysdate-level ,'Day') as DayOfWeek,to_char(trunc(sysdate-level)) as DateofMonth
from dual where to_char(sysdate-level ,'Day') in ('Saturday ','Sunday ')connect by level <= 31

Report message to a moderator

Previous Topic:	Pass variable value in input XML for calling webservice
Next Topic:	please sole it

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 19:22:15 CDT 2024