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 21, 2011 7:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
Comments posted to this topic are about the item Function to get number of days in month
Post #1097311
Posted Tuesday, April 26, 2011 11:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 6:59 AM
Points: 247, Visits: 420
How is this not better?

Select day(dateadd(mm,DateDiff(mm, -1, getdate()),0) -1)



Post #1098842
Posted Tuesday, April 26, 2011 11:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
Thats clever.
Post #1098853
Posted Wednesday, April 27, 2011 1:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 24, 2014 6:50 PM
Points: 401, Visits: 556
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
Post #1099216
Posted Wednesday, April 27, 2011 2:40 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 790, Visits: 646


DECLARE @dtDate DATETIME

SET @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
Post #1099246
Posted Wednesday, April 27, 2011 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 27, 2012 6:32 AM
Points: 21, Visits: 97
How about this?

RETURN DAY(DATEADD(d,-1,DATEADD(mm,DATEDIFF(m,0,@day)+1,0)))
Post #1099395
Posted Wednesday, April 27, 2011 8:16 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
Here is some psuedo code for a correct leap year test calculation.

if year modulo 400 is 0
then is_leap_year
else if year modulo 100 is 0
then not_leap_year
else if year modulo 4 is 0
then is_leap_year
else
not_leap_year

Since this, and other date checking code, is built into the TSQL Date functions I recomend using the examples that have already been posted.
Post #1099487
Posted Wednesday, April 27, 2011 12:52 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 18, 2013 6:12 AM
Points: 952, Visits: 552
Well, since everyone else likes to do subtractions, I thought I'd contribute an addition only solution (if you don't count DATEDIFF as subtraction )

SELECT DATEDIFF(dd,@testdate,(DATEADD(m,1,@testdate)))

Post #1099730
Posted Wednesday, April 27, 2011 3:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 10:55 AM
Points: 8, Visits: 139
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 #1099855
Posted Thursday, April 28, 2011 1:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 2:06 AM
Points: 119, Visits: 9
nice one..
Post #1100018
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse