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

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

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

0	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

Latest Posts