> Since lovely M$ Sql does not have a select last_day function like oracle I > was wondering if anyone had written a script that will take the month > number and return either the number of days or the last day of that month?
Is there a particular reason it needs to be in the db? ... declare @mydate smalldatetime; set @mydate = getdate(); select DateAdd(dd,-(1 + datepart(dd,@mydate)),DateAdd(mm,1,@mydate)); should do it... What you're doing is adding a month to the current date, then subtracting the datepart plus one which will give you the last day of the month for @mydate... If you have SQL 2000 I recommend creating a userdefined function dbo.LastDayOfMonth(@mydate smalldatetime); and possibly dbo.DaysInMonth(@mydate smalldatetime); which will return DatePart(dd,dbo.LastDayOfMonth(@mydate)); ... You can't use GETDATE() within a UDF in SQL ( don't ask me why. :) so if you need it for the current date, you'll have to use dbo.DaysInMonth(GETDATE()); hth Isaac Dealey www.turnkey.to 954-776-0046 ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
