Log in  ::  Register  ::  Not logged in

## Function to get number of days in month

 Author Message harsha.majety Ten Centuries Group: General Forum Members Points: 1169 Visits: 449 Comments posted to this topic are about the item Function to get number of days in month Obs SSC Eights! Group: General Forum Members Points: 865 Visits: 424 How is this not better?Select day(dateadd(mm,DateDiff(mm, -1, getdate()),0) -1) harsha.majety Ten Centuries Group: General Forum Members Points: 1169 Visits: 449 Thats clever. Toby Harman Hall of Fame Group: General Forum Members Points: 3309 Visits: 710 My personal favourite`SELECT DATEPART(dd, DATEADD(dd, DATEPART(dd, DATEADD(mm, 1, @day)) * -1, DATEADD(mm, 1, @day)))` I suspect you would be hard pressed to notice the difference between any of the above, though the Client Statistics suggests the CASE solution is the most efficient Mitesh Oswal SSCrazy Group: General Forum Members Points: 2268 Visits: 653 `DECLARE @dtDate DATETIMESET @dtDate = '2011/02/28'SELECT @dtDate= dateadd(mm,datediff(mm,0,@dtDate),0)SELECT datediff(dd,@dtDate,dateadd(mm,1,@dtDate))` Regards,Mitesh OSwal+918698619998 mpv-1060508 SSChasing Mays Group: General Forum Members Points: 603 Visits: 97 How about this?RETURN DAY(DATEADD(d,-1,DATEADD(mm,DATEDIFF(m,0,@day)+1,0))) SanDroid SSCertifiable Group: General Forum Members Points: 7824 Visits: 1046 Here is some psuedo code for a correct leap year test calculation.if year modulo 400 is 0 then is_leap_yearelse if year modulo 100 is 0 then not_leap_yearelse if year modulo 4 is 0 then is_leap_yearelse not_leap_yearSince this, and other date checking code, is built into the TSQL Date functions I recomend using the examples that have already been posted. Stephen_W_Dodd SSCommitted Group: General Forum Members Points: 1518 Visits: 553 Well, since everyone else likes to do subtractions, I thought I'd contribute an addition only solution :-D (if you don't count DATEDIFF as subtraction :-))`SELECT DATEDIFF(dd,@testdate,(DATEADD(m,1,@testdate)))` Slope SSC Veteran Group: General Forum Members Points: 286 Visits: 143 Of course, if you use a calendars table you never have to worry about having to code or validate any date calculations ever again. :-P Sure makes life a lot simpler. Here is how I would get that answer:`SELECT d.DaysInCalendarMonthFROM dbo.dwCalendars dWHERE d.CalendarDate = @SomeDate` Subramaniyan.Manickam SSC Veteran Group: General Forum Members Points: 215 Visits: 9 nice one..:-)