How to find the number of saturdays and Sundays? [message #195495] |
Fri, 29 September 2006 01:29 |
srinivasocp
Messages: 91 Registered: December 2005 Location: INDIA
|
Member |
|
|
Hi All,
Please let me know the query how to get the saturdays and sundays with Dates in a particular year. Say in year 2005 i have 52 saturdays... I would lkie to get the daets of all that saturdays .
Help me with the query.
Regards
Srini..
|
|
|
|
Re: How to find the number of saturdays and Sundays? [message #195514 is a reply to message #195511] |
Fri, 29 September 2006 02:47 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or, if you fancy providing a suery like the OP asked for, you could use something like this:
select dte, to_char(dte,'DAY') day
from (select to_date('01-jan-2006','dd-mon-yyyy') + level dte
from dual
connect by level <= 365)
where to_char(dte,'DY') in ('SAT','SUN');
|
|
|
Re: How to find the number of saturdays and Sundays? [message #195625 is a reply to message #195514] |
Fri, 29 September 2006 13:59 |
sidagam
Messages: 24 Registered: February 2005 Location: Hyderabad
|
Junior Member |
|
|
Hi All,
The query sent JRowbottom needs to change little bit.
It was not considering the 1st January,2006 which is comes on SUNDAY.
select
dte
, to_char(dte,'DAY') day
from
(
select
to_date('01-jan-2006','dd-mon-yyyy') + (level-1) dte
from dual
connect by level <= 365
)
where to_char(dte,'DY') in ('SAT','SUN');
Thanks & Regards,
Babu SRSB.
|
|
|
Re: How to find the number of saturdays and Sundays? [message #195882 is a reply to message #195625] |
Mon, 02 October 2006 21:18 |
ehegagoka
Messages: 493 Registered: July 2005
|
Senior Member |
|
|
hi!
just wanna ask why using 'day' in the where clause does not return any value?
SQL> select dte, to_char(dte, 'day') day
2 from (select to_date('01-jan-2006', 'dd-mon-rrrr') + (level-1) dte
3 from dual
4 connect by level <=14)
5 where to_char(dte, 'dy') in ('sat', 'sun')
6 /
DTE DAY
----------- ---------
1/1/2006 sunday
1/7/2006 saturday
1/8/2006 sunday
1/14/2006 saturday
SQL>
SQL> select dte, to_char(dte, 'day') day
2 from (select to_date('01-jan-2006', 'dd-mon-rrrr') + (level-1) dte
3 from dual
4 connect by level <=14)
5 where to_char(dte, 'day') in ('saturday', 'sunday')
6 /
DTE DAY
----------- ---------
SQL>
hhmmm i need to use trim to get it
SQL> select dte, to_char(dte, 'day') day
2 from (select to_date('01-jan-2006', 'dd-mon-rrrr') + (level-1) dte
3 from dual
4 connect by level <=14)
5 where trim(to_char(dte, 'day')) in ('saturday', 'sunday')
6 /
DTE DAY
----------- ---------
1/1/2006 sunday
1/7/2006 saturday
1/8/2006 sunday
1/14/2006 saturday
[Updated on: Mon, 02 October 2006 21:20] Report message to a moderator
|
|
|
|
|