List of Months & Week Days Between 2 Dates [Resolved]

Posted by Praveenjha under Sql Server on 4/28/2013 | Points: 10 | Views : 3195 | Status : [Member] | Replies : 4
Hi All,

I want to find list of months & Week days between 2 dates.

for example, From Date - 10/04/2013 To Date - 05/05/2013

expected output :

Months Days

------- -----

Apr 15

May 03


I have seen the post related to my query at

http://www.dotnetfunda.com/forums/thread10146-list-of-months-days-between-2-dates.aspx?com=added

It helped me a lot.

But i got to implement a new scenario.
Instead of count of days, i need to count the week days i.e., need to exclude Saturday and Sunday while counting days.

Please help me.

Thanks & Regards,

Praveen




Responses

Posted by: Kirthiga on: 4/29/2013 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi Praveenjha,

Try this code I hope this will satisfy your need

Declare @StartDay datetime, @EndDay datetime

Set @StartDay='2013-04-10'
Set @EndDay='2013-05-05'

;with cte(Date) as
(
select @StartDay
union all
select Date+1 from cte where Date < @EndDay
)
select DATENAME(M,Date)Months,count(Date)[No Of Working Days] from cte
where DATENAME(W,Date) not in ('sunday','saturday')
group by DATENAME(M,Date)


Praveenjha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Praveenjha on: 4/29/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Kirthiga,


A very very big THANKYOU Kirthiga.

you save my life. your code is working fine for me.

Thankyou so much again.

cheers,

Praveen

Praveenjha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Praveenjha on: 4/29/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Kirthiga,

one more thing i need to ask. i have to run this code in SQL Server 2005 as well as SQL Server 2000.

In SQL Server 2005, your code is working fine. bt in SQL Server 2000 its not working as cte functionality introduce in SQL Server 2005.

Can you please help me out to modify this code according to SQL Server 2000.

Thankyou.

Cheers,
Praveen

Praveenjha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 4/30/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Praveenjha,

You can try this using cursor in Sql Server 2000.

The basic logic is to arrive Day wise record between Start Day and End Day.

Praveenjha, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response