Number of days in each month excluding sundays

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Number of days in each month excluding sundays

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-04-24 : 22:54:45

I am kind of stuck.. I am trying to calculate the number of days in each month excluding Sundays. How can I do this?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-24 : 23:46:46

check this forum, http://www.sqlteam.com/forums/forum.asp?FORUM_ID=11

--------------------
keeping it simple...

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-25 : 01:42:55

See if this works, given a starting and ending date:

declare @startdate datetime,
@enddate datetime

set @startdate = convert(datetime, '20050401')
set @enddate = convert(datetime, '20050430')

SELECT
DATEDIFF(d, DATEADD(d, CASE WHEN DATEPART(dw, @startdate) = 7 THEN 1 ELSE 0 END, @startdate), DATEADD(d, CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END, @enddate))
- DATEDIFF(wk, DATEADD(d, CASE WHEN DATEPART(dw, @startdate) = 7 THEN 1 ELSE 0 END, @startdate), DATEADD(d, CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END, @enddate))

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-25 : 06:42:43

This does it (I think)...

--inputs
declare @startdate datetime, @enddate datetime
set @startdate = '20000101'
set @enddate = '20091231'

--calculation
declare @NumberOfDays int
set @NumberOfDays = datediff(d, @startdate, @enddate) + 1
set datefirst 7
set rowcount @NumberOfDays

declare @numbers table (i int identity(0,1), x bit)
insert into @numbers select null from master.dbo.sysobjects a, master.dbo.sysobjects b, master.dbo.sysobjects c
set rowcount 0

select datepart(year, d) as year, datepart(month, d) as month, count(d) as 'non-sundays'
from
    (select dateadd(dd, i, @startdate) d from @numbers) dates
where not datepart(dw, d) = 1 group by datepart(year, d), datepart(month, d)
order by 1, 2

Make sure you check out the link jen posted though - there's tons (literally) of useful date related stuff (mainly posted by Michael Valentine Jones).

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-04-26 : 11:02:29

Thanks, but I am quite understand why you are using system tables?
select null from master.dbo.sysobjects a, master.dbo.sysobjects b, master.dbo.sysobjects c

Is there any way that I can do this without using system tables?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 11:21:06

Try this code with the F_TABLE_DATE function.

Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


select
	NotSunday = count(*)
from
	dbo.F_TABLE_DATE('2006-04-01','2006-04-30')
where
	DAY_OF_WEEK <> 1

Results:


NotSunday   
----------- 
25

(1 row(s) affected)

CODO ERGO SUM

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-04-26 : 11:48:58

thanks, but is there any way I canjust pass the year ( i.e 2005) and it returns all the months in that year?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 12:03:34

quote:
Originally posted by jung1975

thanks, but is there any way I canjust pass the year ( i.e 2005) and it returns all the months in that year?

Yes, you should be able to work that out from the query I posted.

You should state what you are really after to begin with, instead of saying you want to do one thing, and then coming back with a different requirement.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 21:00:14

quote:
Originally posted by jung1975

thanks, but is there any way I canjust pass the year ( i.e 2005) and it returns all the months in that year?

Use MVJ's code and add GROUP BY

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-27 : 04:39:47

Jung,

The sysobjects thing was just to get a table of numbers. But if you use Michael's table of dates you can do (for your latest requirement, and as khtan has pointed out)...

select
  month, count(*) as 'non-sundays'
from
  dbo.F_TABLE_DATE('20050101', '20051231')
where
  DAY_OF_WEEK <> 1
group by month

(And thanks Michael for pointing out that I was getting my wires crossed with this and a similar post.)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 05:01:28

jung,
use [YEAR_MONTH] instead of [MONTH] if you have cross year date.


select
  [YEAR_MONTH], count(*) as 'non-sundays'
from
  dbo.F_TABLE_DATE('20050101', '20051231')
where
  DAY_OF_WEEK <> 1
group by [YEAR_MONTH]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources