Another date question - 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

Transact-SQL (2000)

Another date question

Author

Topic

makimark
Starting Member

34 Posts

Posted - 2003-12-09 : 04:21:20

Hi

Is there a function to return the number of days for a given month ?
Lets say myfunction(12) (12 being the month number) and the output should be 31 days ?

Datediff works but requires a start and end date. Ideally i would only like to pass a month number.

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-09 : 04:46:02

Leap years?

select datepart(dd,dateadd(dd,-1,dateadd(mm,1,stuff(convert(varchar(8),getdate(),112),5,4,right('00'+convert(varchar(2),@mth),2)+'01'))))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-09 : 10:20:32

I know you indicate that you would only want to pass in the month, but where would you get that from?

Probably a datetime column...

SELECT DAY(DATEADD(m, DATEDIFF(m, 0, GetDate())+1, 0)-1)

Brett

8-)

TimChenAllen
Starting Member

45 Posts

Posted - 2003-12-10 : 04:04:56

Here you go:


CREATE FUNCTION [dbo].[numberDays] (@month int, @year int)  
RETURNS int AS  
BEGIN 
return day(dateadd(dd, -1, dateadd(mm, 1, cast(@month as varchar) + '/01/' + cast(@year as varchar))))
END

You have to have the year for this function (can you think of why?). If you don't want to call it with the year, call it like this:


dbo.numberDays(12, year(getdate()))

--
Timothy Chen Allen
email me if you have a job in New Orleans for me
[url]http://www.timallen.org[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources