## Function to get number of days in month

 Author Message Obs SSC Veteran Group: General Forum Members Points: 249 Visits: 424 Well played!Slope (4/27/2011)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` SanDroid Ten Centuries Group: General Forum Members Points: 1410 Visits: 1046 Obs (4/28/2011)Well played!Slope (4/27/2011)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`Only if the Calendars table was created using a LOT differant math than the one presented for this function. Toby Harman SSC-Addicted Group: General Forum Members Points: 481 Visits: 668 Back when school was cool (and storage expensive!) they taught us not to store calculated information.I suspect that all of the functions presented here are lower cost than a read. MrReddy Valued Member Group: General Forum Members Points: 57 Visits: 553 how to give any mnth to the above query? damir.sovic SSC Rookie Group: General Forum Members Points: 39 Visits: 17 Does anyone noticed this logic:Month < 8 AND month <> 2 => days_in_month = 30 + (month & 1)Month >= 8 => days_in month = 30 + ((month+1) & 1)Of course, there is standard for the February cyric57-net Forum Newbie Group: General Forum Members Points: 2 Visits: 17 And why not:Declare @day DATETIME Set @day='2011-02-15'select datediff(day,dateadd(d,1-day(@day),@day),dateadd(m,1,dateadd(d,1-day(@day),@day))):-) Slope Forum Newbie Group: General Forum Members Points: 8 Visits: 143 ramanamreddy (5/2/2011)how to give any mnth to the above query?Apologies for the late reply:`SELECT DISTINCT d.DaysInCalendarMonthFROM dbo.dwCalendars dWHERE d.CalendarYear = 2011AND d.CalendarMonthName = 'February'` EITCL Valued Member Group: General Forum Members Points: 71 Visits: 442 This one doesn't work unless the @testdate is the 1st day of the month. E.g. with @testdate = '2011-01-30' it incorrectly returns 29. SanDroid Ten Centuries Group: General Forum Members Points: 1410 Visits: 1046 EITCL (6/20/2011)This one doesn't work unless the @testdate is the 1st day of the month. E.g. with @testdate = '2011-01-30' it incorrectly returns 29.There are several things wrong with the information in the article.There are many posts in the discussion thread that show better ways of converting a date. gwolbr Forum Newbie Group: General Forum Members Points: 1 Visits: 175 If you change the last bit of the case to:else case when ((Year(@day) % 4 = 0) and ((Year(@day) % 100 != 0) or (Year(@day) % 400 = 0)))you will perform fewer tests. With ((A and B) or C), both A and C have to be evaluated even when A is false. When changed to (A and (B or C)), B and C are only evaluated when A is true.