Function to get number of days in month

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Harsha Majety » Function to get number of days in month

20 posts, Page 1 of 2

1 2 »»

Function to get number of days in month

Rate Topic

Display Mode

Topic Options

Author

Message

harsha.majety

Posted Thursday, April 21, 2011 7:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 1:29 PM
Points: 6, Visits: 399

Comments posted to this topic are about the item Function to get number of days in month

Post #1097311

Obs

Posted Tuesday, April 26, 2011 11:21 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 4:01 PM
Points: 247, Visits: 395

How is this not better?

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

Post #1098842

harsha.majety

Posted Tuesday, April 26, 2011 11:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 1:29 PM
Points: 6, Visits: 399

Thats clever.

Post #1098853

Toby Harman

Posted Wednesday, April 27, 2011 1:04 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:38 PM
Points: 322, Visits: 477

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

Mitesh Oswal

Posted Wednesday, April 27, 2011 2:40 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:46 AM
Points: 606, Visits: 373



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))

Post #1099246

mpv-1060508

Posted Wednesday, April 27, 2011 7:14 AM

Grasshopper

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

SanDroid

Posted Wednesday, April 27, 2011 8:16 AM

Ten 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

Stephen_W_Dodd

Posted Wednesday, April 27, 2011 12:52 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:42 PM
Points: 826, Visits: 540

Well, since everyone else likes to do subtractions, I thought I'd contribute an addition only solution BigGrin

(if you don't count DATEDIFF as subtraction Smile

)

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

Post #1099730

Slope

Posted Wednesday, April 27, 2011 3:29 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 08, 2013 5:27 PM
Points: 8, Visits: 135

Of course, if you use a calendars table you never have to worry about having to code or validate any date calculations ever again. Tongue

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

Subramaniyan.Manickam

Posted Thursday, April 28, 2011 1:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 05, 2012 2:06 AM
Points: 119, Visits: 9

nice one.. Smile

Post #1100018

« Prev Topic | Next Topic »

20 posts, Page 1 of 2

1 2 »»

Permissions