plz help to count day in a month

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

General SQL Server Forums

New to SQL Server Programming

plz help to count day in a month

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-09 : 13:15:29

i need to get the number of days in a month without saturday
and my fixed holliday w/c is on my holliday_table

plz......help..........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 13:32:30

[code]SET DATEFIRST 7
DECLARE @Date datetime
SET @Date='1 Apr 2008'--example value
SET @MonthDate=DATEADD(mm,DATEDIFF(mm,0,@Date),0)

SELECT COUNT(DateVal) FROM
(
SELECT DATEADD(dd,v.number,@MonthDate) AS DateVal
FROM master..spt_values v
LEFT JOIN Holidaytable h
ON DATEADD(dd,DATEDIFF(dd,0,h.Day),0)= DATEADD(dd,v.number,@MonthDate)
WHERE v.type='p'
AND DATEADD(dd,v.number,@MonthDate)<=DATEADD(mm,1,@MonthDate)
AND h.Day IS NULL
AND DATEPART(dw,DATEADD(dd,v.number,@MonthDate)) <> 7
)t[/code]

passing any date to @Date will give total days in that month barring saturday and also those days of month in Holiday table

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-09 : 13:37:41

thank you so much........

......................it a big help for my thesis................

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 13:40:02

quote:
Originally posted by wormz666

thank you so much........

......................it a big help for my thesis................

if you want to exclude sundays also as in here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112283

you need small modification

SET DATEFIRST 7
DECLARE @Date datetime
SET @Date='1 Apr 2008'--example value
SET @MonthDate=DATEADD(mm,DATEDIFF(mm,0,@Date),0)

SELECT COUNT(DateVal) FROM
(
SELECT DATEADD(dd,v.number,@MonthDate) AS DateVal
FROM master..spt_values v
LEFT JOIN Holidaytable h
ON DATEADD(dd,DATEDIFF(dd,0,h.Day),0)= DATEADD(dd,v.number,@MonthDate)
WHERE v.type='p'
AND DATEADD(dd,v.number,@MonthDate)<=DATEADD(mm,1,@MonthDate)
AND h.Day IS NULL
AND DATEPART(dw,DATEADD(dd,v.number,@MonthDate)) NOT IN (1,7)
)t

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-09 : 13:42:32

NOT IN (1,7)
does 1 represent sunday and 7 is saturday???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 13:49:50

quote:
Originally posted by wormz666

NOT IN (1,7)
does 1 represent sunday and 7 is saturday???

yup. as long as DATEFIRST setting is 7

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-09 : 14:01:18

quote:
Originally posted by wormz666

NOT IN (1,7)
does 1 represent sunday and 7 is saturday???

also depends on the locality of your database. the above is US format.
ISO format considers 1 as Monday and 7 as Sunday.

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-26 : 00:07:43

the query runs good but there's a problem
when then next first day of the month is on the range of monday to friday
it will count...i only want to know then day in a month
and eliiminate saturday and sunday.........

please help.......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 01:00:18

quote:
Originally posted by wormz666

the query runs good but there's a problem
when then next first day of the month is on the range of monday to friday
it will count...i only want to know then day in a month
and eliiminate saturday and sunday.........

please help.......

modify as below

SET DATEFIRST 7
DECLARE @Date datetime
SET @Date='1 Apr 2008'--example value
SET @MonthDate=DATEADD(mm,DATEDIFF(mm,0,@Date),0)

SELECT COUNT(DateVal) FROM
(
SELECT DATEADD(dd,v.number,@MonthDate) AS DateVal
FROM master..spt_values v
LEFT JOIN Holidaytable h
ON DATEADD(dd,DATEDIFF(dd,0,h.Day),0)= DATEADD(dd,v.number,@MonthDate)
WHERE v.type='p'
AND DATEADD(dd,v.number,@MonthDate)<DATEADD(mm,1,@MonthDate)
AND h.Day IS NULL
AND DATEPART(dw,DATEADD(dd,v.number,@MonthDate)) NOT IN (1,7)
)t

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-26 : 01:26:02

@Date = '1 Apr 2008'
if 1 jun 2008 is on the range of monday and friday
it will count.....

i have no idea where will i modify it....

and also where is that master table get?....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 01:31:44

quote:
Originally posted by wormz666

@Date = '1 Apr 2008'
if 1 jun 2008 is on the range of monday and friday
it will count.....

i have no idea where will i modify it....

and also where is that master table get?....

it wont. i have changed <= to <. Did you tried modified version i posted?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 01:34:18

master..spt_values is an internal table available within SQL Server which is used as a count table in above case.

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-26 : 01:40:32

i have not tried because i am on the cafe......

i only work for my group mate in schools

there no ms sql here in the cafe.......

there a lots of things that i should explore in sql server.....

thanks a lot on the info.............

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 01:45:19

quote:
Originally posted by wormz666

i have not tried because i am on the cafe......

i only work for my group mate in schools

there no ms sql here in the cafe.......

there a lots of things that i should explore in sql server.....

thanks a lot on the info.............

ok. try it out and let me know if you have any more problems
you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources