Days Per Month - SQL Server Forums

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

SQL Server Development (2000)

Days Per Month

Author

Topic

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-09-01 : 11:47:44

Any idea of how I can code a proc. to know how many days are in a month without telling it?

Derrick

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 12:11:26

i don't know any sql function for that, but this shoul do it:

declare @Month int
set @Month = 5

declare @NumOfDayes table (monthNum int, dayNum int)
insert @NumOfDayes
select 1, 31 union all
select 2, (select case when
(year(getdate())%4 = 0) or
(year(getdate())%100 = 0) and
(year(getdate())%400 = 0) then 29
else 28
end) union all --this fixes leap years
select 3, 31 union all
select 4, 30 union all
select 5, 31 union all
select 6, 30 union all
select 7, 31 union all
select 8, 31 union all
select 9, 30 union all
select 10, 31 union all
select 11, 30 union all
select 12, 31

select dayNum
from @NumOfDayes
where MonthNum = @Month

Go with the flow & have fun! Else fight the flow :)

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 12:15:07

[code]
USE Northwind
GO

CREATE FUNCTION udf_LastDay (@x datetime)
RETURNS datetime
BEGIN
DECLARE @y datetime
SELECT @y = DATEADD(d,-1,CONVERT(char(2),MONTH(DATEADD(mm,1,@x)))+'/01/'+CONVERT(char(4),YEAR(@x)))
RETURN @y
END
GO

SELECT dbo.udf_LastDay(GetDate())
GO

[/code]

But I thought we had found a simpler way....

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 12:33:48

or even simpler

declare @date datetime
set @date = getdate()
select day(dateadd(m, 1, dateadd(d, -day(@date), @date)))

Go with the flow & have fun! Else fight the flow :)

xptm
Starting Member

7 Posts

Posted - 2004-09-01 : 12:40:55

Look here:

[url]http://www.informit.com/articles/article.asp?p=30939&seqNum=9[/url]

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 15:49:34

quote:
Originally posted by WalkerDA

Any idea of how I can code a proc. to know how many days are in a month without telling it?

Derrick

What do you mean by, "Not telling it"?

Brett

8-)

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-09-01 : 19:11:56

Sorry guys been in meetings all day... By not telling it would be by using functions as in spirit1's example or even the udf you designed.

This has been one of those days..thanks!

Derrick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources