October 20, 2014 at 9:13 am
Comments posted to this topic are about the item Get month days
Igor Micev,My blog: www.igormicev.com
October 29, 2014 at 6:08 am
CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATETIME) RETURNS INT
AS
BEGIN
return DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, @mydate) , 0) ,DATEADD(d, -1, DATEADD(m, 1, DATEADD(month, DATEDIFF(month, 0, @mydate) , 0)))) + 1
END
go
select dbo.fnGetMonthDays3('2/1/2000')
-- Returns 29
-- It's kludgy, but it works.
October 30, 2014 at 3:27 am
robolance (10/29/2014)
CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATETIME) RETURNS INTAS
BEGIN
return DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, @mydate) , 0) ,DATEADD(d, -1, DATEADD(m, 1, DATEADD(month, DATEDIFF(month, 0, @mydate) , 0)))) + 1
END
go
select dbo.fnGetMonthDays3('2/1/2000')
-- Returns 29
-- It's kludgy, but it works.
Yeah, I know this trick, it's just ok.
Thanks for enriching with another function about the topic.
Igor Micev,My blog: www.igormicev.com
October 31, 2014 at 6:08 am
I ran both versions against a DB table with about 1.2 million rows.
Without the get days it took 35 seconds.
With Igor's version it took 39 seconds.
With my version it took 39 seconds.
Not really a 'scientific' test, but a quick indicator.
I kinda expected that they would be very similar. It is just a matter taste.
November 1, 2014 at 11:08 am
robolance (10/31/2014)
I ran both versions against a DB table with about 1.2 million rows.Without the get days it took 35 seconds.
With Igor's version it took 39 seconds.
With my version it took 39 seconds.
Not really a 'scientific' test, but a quick indicator.
I kinda expected that they would be very similar. It is just a matter taste.
Very good from your side. I've been using that way for a NoSQL db, and it worked well.
Thanks!
Igor Micev,My blog: www.igormicev.com
November 4, 2014 at 4:09 am
Hi,
This is Simple Function to get Month days for current Month
/***********************************************/
/***********************************************/
/***********************************************/
CREATE FUNCTION dbo.FnDaysOfCurrentMonth(@myDate DATE) RETURNS INT
AS
BEGIN
DEclare @Month int
DECLARE @days int
select @month=month(@myDate)
IF @Month =2
BEGIN
IF (Year(@myDate)%4)=0
BEGIN
SET @days=29
END
ELSE
BEGIN
set @days=28
END
END
ELSE
BEGIN
select @days =day(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@myDate)+1,0)))
END
RETURN @days
END
November 7, 2014 at 9:41 am
Igor,
I really think you're overcomplicating it. From my point of view it is much simpler to add one month to the date and then subtract the day number, which brings you back to the last day of the previous month. Then it's only a matter of extracting the day (which must be equal to the number of days of the month of the supplied date).
CREATE FUNCTION dbo.fn_GetMonthDays
(
@InputDate DateTime
)
RETURNS int
AS
BEGIN
RETURN DAY(DATEADD(m,1,@InputDate) - DAY(DATEADD(m,1,@InputDate)))
END
I find it simpel and elegant.
November 10, 2014 at 1:22 am
What about this function with this date format?
CREATE FUNCTION dbo.[fnGetMonthDays]
(
@DATE DATE
)
RETURNS INT
AS
BEGIN
RETURN DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CONVERT(DATE,@DATE,101))))
END
SELECT dbo.fnGetMonthDays(CONVERT(DATE,'Feb 2012'))
--29
rkaram
November 10, 2014 at 1:46 am
Thanks to All that contributed on the discussion. Everybody that will need such function will have to choose from the many versions of the function for returning days in a month.
Igor Micev,My blog: www.igormicev.com
November 10, 2014 at 4:30 am
CREATE FUNCTION dbo.[fnGetMonthDays1]
(
@DATE DATE
)
RETURNS INT
AS
BEGIN
RETURN DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CONVERT(DATE,@DATE,101))))
END
will not work, check:
SELECT dbo.fnGetMonthDays1(CONVERT(DATE,'31 Jan 2012'))
November 10, 2014 at 4:34 am
You have to use the date format like that "Feb 2012" without the 'Day'
go try it and enjoy it 🙂
Rabih
rkaram
November 10, 2014 at 7:16 am
rabih_karam (11/10/2014)
What about this function with this date format?
CREATE FUNCTION dbo.[fnGetMonthDays]
(
@DATE DATE
)
RETURNS INT
AS
BEGIN
RETURN DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CONVERT(DATE,@DATE,101))))
END
SELECT dbo.fnGetMonthDays(CONVERT(DATE,'Feb 2012'))
--29
This doesn't work. Test it for '2012-2-28 5:33:21'.
November 10, 2014 at 7:29 am
Kindly read my previous post and the way that you should send the param @date
the @date should be send in this format 'Feb 2012' without the DAY
rkaram
November 10, 2014 at 7:42 am
My apologies. I missed that line in your post where you mentioned the difference in format. I thought you were talking about the same thing everyone else was talking about. Again, I apologize.
November 13, 2014 at 5:29 am
And just to mention the Microsoft supplied function for SQL version 2012, 2014 the EOMONTH.
http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(EOMONTH_TSQL);k(SQL12.SWB.TSQLRESULTS.F1);k(SQL12.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply