Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Function to get number of days in month Expand / Collapse
Author
Message
Posted Thursday, April 28, 2011 7:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 07, 2014 1:46 PM
Points: 247, Visits: 419
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. Sure makes life a lot simpler. Here is how I would get that answer:

SELECT d.DaysInCalendarMonth
FROM dbo.dwCalendars d
WHERE d.CalendarDate = @SomeDate




Post #1100210
Posted Thursday, April 28, 2011 7:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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. Sure makes life a lot simpler. Here is how I would get that answer:

SELECT d.DaysInCalendarMonth
FROM dbo.dwCalendars d
WHERE d.CalendarDate = @SomeDate



Only if the Calendars table was created using a LOT differant math than the one presented for this function.
Post #1100215
Posted Thursday, April 28, 2011 7:17 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:46 PM
Points: 381, Visits: 535
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.
Post #1100687
Posted Monday, May 02, 2011 12:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 21, 2014 2:10 PM
Points: 57, Visits: 534
how to give any mnth to the above query?
Post #1101447
Posted Monday, May 02, 2011 3:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 3:53 AM
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
Post #1101958
Posted Monday, June 13, 2011 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 21, 2013 3:15 PM
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)))

Post #1124221
Posted Monday, June 13, 2011 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 09, 2013 10:55 AM
Points: 8, Visits: 139
ramanamreddy (5/2/2011)
how to give any mnth to the above query?


Apologies for the late reply:

SELECT DISTINCT d.DaysInCalendarMonth
FROM dbo.dwCalendars d
WHERE d.CalendarYear = 2011
AND d.CalendarMonthName = 'February'

Post #1124510
Posted Monday, June 20, 2011 5:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 07, 2014 5:54 AM
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.
Post #1128097
Posted Monday, June 20, 2011 7:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.
Post #1128212
Posted Thursday, September 01, 2011 7:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 1:10 PM
Points: 1, Visits: 160
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.

Post #1168670
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse