SQLServerCentral / SQL Server 2005 / SQL Server Newbies / Last Day of any Month / Latest Posts

RE: Last Day of any Month

dfine — Mon, 13 Dec 2010 08:35:58 GMT

Try this !! SELECT CASE WHEN DAY(DATEADD(D,1,GETDATE()))=1 THEN 'MONTH END' ELSE 'NOT MONTH END' END AS PERIODHope this helps.

RE: Last Day of any Month

sreid08 — Sat, 05 Sep 2009 10:17:21 GMT

[quote][b]Jeff Moden (9/4/2009)[/b][hr][quote][b]sreid08 (9/4/2009)[/b][hr]I have this page bookmarked because I can never remember how to get the last day of the month -- or some other date functions. It is very helpful:http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/[/quote]Please see my previous post 2 posts above. ;-) The method at that site misses a whole second of the day and if you have data with times, it could mean that you miss a whole lot of data. Don't use that method... ever.[/quote]Hi Jeff -- thank you very much! I have only one report that I am using this in - and it is used to find the last day of the month 3 months ago. I will go and change the code AND delete that web page from my bookmarks!Thanks Again!SReid

RE: Last Day of any Month

Michael Valentine Jones — Sat, 05 Sep 2009 00:48:54 GMT

Here is another series of tests showing the difference in performance of inline code vs. scalar UDFs.Demo Performance Penalty of User Defined Functions[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78601[/url]The functions in the following links have the best available methods I could find that work for the maximum possible datetime ranges for finding start of time periods, and the last day of time periods. Most can be converted to be run as inline code.Finding the Start of Time PeriodsOne of the most common questions is how to remove the time from a datetime so that you end up with just a date. In other words, change 2006/12/13 02:33:48.347 to 2006/12/13 00:00:00.000. The following links have functions that will find the start of Century, Decade, Year, Quarter, Month, Week, Day, Hour, 30 Minutes, 20 Minutes, 15 Minutes, 10 Minutes , 5 Minutes , x number of Minutes ,Minute , or Second.Start of Time Period Functions:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755[/url]Start of Week Function:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307[/url]Finding the End of Time PeriodsSometimes there is a need to find the last day of a time period. The following links have functions that will find the last day of Century, Decade, Year, Quarter, Month, or Week.End Date of Time Period Functions:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759[/url]End of Week Function:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760[/url]For a collection of other links related to datetime in SQL Server, see this:Date/Time Info and Script Links[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762[/url]

RE: Last Day of any Month

Jeff Moden — Fri, 04 Sep 2009 23:37:31 GMT

Folks, don't even think of using a user defined function for this. Although it's convenient and you don't have to teach newbies why it works and they don't have to memorize the code to do it, it's just not worth the performance hit. Using a user defined function for this is a whole lot slower than just learning how to do it with inline code.Let's prove it... here's my normal million row table. Don't let it scare you. It doesn't take any time at all to build...[code="sql"]--===== Create and populate a 1,000,000 row test table. -- Column "RowNum" has a range of 1 to 100,000 unique numbers -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' -- for all rows. -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F) -- Jeff Moden SELECT TOP 1000000 SomeID = IDENTITY(INT,1,1), SomeInt = ABS(CHECKSUM(NEWID()))%50000+1, SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65) + CHAR(ABS(CHECKSUM(NEWID()))%26+65), SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)), SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY), SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME), SomeHex12 = RIGHT(NEWID(),12) INTO #JBMTest FROM Master.dbo.SysColumns t1 CROSS JOIN Master.dbo.SysColumns t2 --===== Add a primary key just because ALTER TABLE #JBMTest ADD PRIMARY KEY CLUSTERED (SomeID)[/code]... and here's a function made from the efficient method Michael Valentine Jones made for finding the last day of the month...[code="sql"] CREATE FUNCTION dbo.LastDayOfMonth (@AnyDate DATETIME)RETURNS DATETIME AS BEGIN RETURN DATEADD(mm,DATEDIFF(mm,-1,@AnyDate),-1) END[/code]... and here's a test where we dump the calculation to a dummy variable to take the display speed out of the picture and some stats code to measure the difference...[code="sql"] SET NOCOUNT ONDECLARE @BitBucket DATETIME PRINT '========== Inline method ==========' SET STATISTICS TIME ON SELECT @BitBucket = DATEADD(mm,DATEDIFF(mm,-1,SomeDate),-1) FROM #JBMTest SET STATISTICS TIME OFF PRINT '========== UDF method ==========' SET STATISTICS TIME ON SELECT @BitBucket = dbo.LastDayOfMonth(SomeDate) FROM #JBMTest SET STATISTICS TIME OFF[/code]Last, but not least, here's the results from my humble desktop...[code]========== Inline method ========== SQL Server Execution Times: CPU time = 1172 ms, elapsed time = 4311 ms.========== UDF method ========== SQL Server Execution Times: CPU time = 80047 ms, elapsed time = 147857 ms.[/code]Heh... try it with TaskMgr running and see what it really does to the CPU and the Kernel. A UDF for this very simple thing to memorize just isn't worth it. ;-)

RE: Last Day of any Month

Jeff Moden — Fri, 04 Sep 2009 22:16:31 GMT

[quote][b]Steve Thompson (9/3/2009)[/b][hr]However, I have one question. This method is contingent on the ability to cast the value 0 as a datetime = 01/01/1900, and -1 as 12/31/1899. Is this a stable standard (pardon the alliteration)? In other words, is it possible that MS could change something in the way they process dates that would cause this to break?[/quote]Sure they could change the standard for date serial numbers... it would cause Excel, SQL Server, and a whole bunch of other MS products to break, though. They probably won't spend the money to change it for any reason because they spent enough to make it work correctly for Y2k and to fix the old bug that made the last day of Feb 1900 the 29th. 1900 wasn't a leap year.Since SQL Server 2008 follows the same "base date serial number" scheme, I'd bet that you're pretty safe another decade or two. :-)

RE: Last Day of any Month

Jeff Moden — Fri, 04 Sep 2009 22:09:31 GMT

[quote][b]sreid08 (9/4/2009)[/b][hr]I have this page bookmarked because I can never remember how to get the last day of the month -- or some other date functions. It is very helpful:http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/[/quote]Please see my previous post 2 posts above. ;-) The method at that site misses a whole second of the day and if you have data with times, it could mean that you miss a whole lot of data. Don't use that method... ever.

RE: Last Day of any Month

Jeff Moden — Fri, 04 Sep 2009 22:07:45 GMT

[quote][b]lefrancisco1 (8/26/2009)[/b][hr]Hi Folks,How to get the Last Day of any month, .bUT FOR EVERY MONTH I SHOULD GETLast DAYEXAMPLE; LastDay of the month August is 31 with their datename(week day) LastDay of the month February is 28 with their datename(week day) ...likewise [/quote]Ummmm.... which year?

RE: Last Day of any Month

Jeff Moden — Fri, 04 Sep 2009 22:01:51 GMT

[quote][b]ps (8/26/2009)[/b][hr][code] SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),Casedatepart(dw, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))When 0 Then 'Sunday'when 1 then 'Monday'When 2 then 'Tuesday'when 3 Then 'Wednesday'When 4 Then 'Friday'When 5 Then 'Saturday'End as Day[/code]Refer - [url] http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/[/url][/quote]Heh... Ya just gotta love a 4 day work week. :-P Also, there's no need for the CASE statement. For example, SELECT DATENAME(dw, GETDATE())I'll also add that I take exception to what's in the link you provided. Finding the first of the next month and subtracting 1 whole second means that you miss out on almost a second of information. It's a VERY bad way to do things.

RE: Last Day of any Month

sreid08 — Fri, 04 Sep 2009 15:50:19 GMT

I have this page bookmarked because I can never remember how to get the last day of the month -- or some other date functions. It is very helpful:http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

RE: Last Day of any Month

Lynn Pettis — Fri, 04 Sep 2009 09:03:51 GMT

Here is some code for you all to play with. Use at your own risk. I tested this with my Tally table.[code]create function dbo.tvf_EOM ( @iDate datetime)returns tableas return select dateadd(mm, datediff(mm, 0, @iDate) + 1, -1) as EndOfThisMonth, datename(dw,dateadd(mm, datediff(mm, 0, @iDate) + 1, -1)) as DatenameEndOfThisMonth;goselect dateadd(dd, t.N - 1, 0) as TheDate, EndOfThisMonth, DatenameEndOfThisMonthfrom dbo.Tally t cross apply dbo.tvf_EOM(dateadd(dd, t.N - 1, 0))where t.N between datediff(dd, 0, getdate()) and datediff(dd, 0, getdate()) + 120;[/code]

RE: Last Day of any Month

zubamark — Fri, 04 Sep 2009 08:04:42 GMT

Try thisSELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'

RE: Last Day of any Month

mstjean — Thu, 03 Sep 2009 10:52:50 GMT

Here's a simple scalar udf that gets the last day of month(LDOM). While the code is not "obvious" I don't consider the obtuse syntax a readability issue because it's well tested and commented in my environment-- and it's off in a udf.[code]CREATE FUNCTION [dbo].[udfLDOM] (@dd datetime) -- in: datetime out: LDOM (with time component stripped off)RETURNS datetime AS BEGIN RETURN dateadd(day,-1,dateadd(month,1,dateadd(month,datediff(month,0,@dd),0))) -- NOTE replace the line above with a modded version of the last line of code on the previous post; it does it in 2 function calls instead of my 4!END[/code]BE AWARE udfs can dramatically slow your code if you are processing a large resultset. If you are doing this to set a page header or similar-- not a problem. But if you are SELECTing 10 million rows, each of which has a date and you are calling one or more udfs it may be time for a nap.To get the day of the week "name" for SOMEDATE:SELECT datename(weekday, SOMEDATE )If you have to do this all in one step you could put both steps into another scalar udf (but then you'd need to collapse the date and the character day name into a string) or a stored procedure with 2 OUTPUT parms. But how you need to use this routine would dictate which if either of these is practical.

RE: Last Day of any Month

Lynn Pettis — Thu, 03 Sep 2009 09:38:21 GMT

[quote][b]dmw (9/2/2009)[/b][hr]A small correction ...[code]declare @ThisDate datetime;set @ThisDate = getdate();select getdate() as CurrentDateTime, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) as BeginningOfNextMonth, -- Beginning of next month dateadd(mm, datediff(mm, 0, @ThisDate) + 1, -1) as EndOfThisMonth -- End of this month[/code][/quote]Thanks. It took me a bit to find the "small" mistake.

RE: Last Day of any Month

Michael Valentine Jones — Thu, 03 Sep 2009 09:23:16 GMT

Conversion of 0 or other numbers directly to datetime is documented in SQL Server Books Online.[code="sql"]select DT, FirstOfMonth = dateadd(mm,datediff(mm,0,a.DT),0), LastOfMonth = dateadd(mm,datediff(mm,-1,a.DT),-1)from ( --Test Data select DT = getdate() union all select DT = '20080214 14:37:25.867' ) a[/code]Results:[code]DT FirstOfMonth LastOfMonth----------------------- ----------------------- -----------------------2009-09-03 11:23:58.503 2009-09-01 00:00:00.000 2009-09-30 00:00:00.0002008-02-14 14:37:25.867 2008-02-01 00:00:00.000 2008-02-29 00:00:00.000[/code]

RE: Last Day of any Month

Steve Thompson-454462 — Thu, 03 Sep 2009 09:00:06 GMT

I always used to handle the EndOfLastMonth problem by stripping the Month and Year out of the date as strings and using them to build the FirstOfNextMonth date, then using DATEADD to move back a day. It always felt a little clunky (and all the CASTs made the code less readable), so I like the much cleaner method proposed by Lynn (which is similar to what ps posted, as well).However, I have one question. This method is contingent on the ability to cast the value 0 as a datetime = 01/01/1900, and -1 as 12/31/1899. Is this a stable standard (pardon the alliteration)? In other words, is it possible that MS could change something in the way they process dates that would cause this to break? I imagine using 0 and -1 would be safer than using 01/01/1900 and 12/31/1899, as 0 would represent Day 0, no matter what day that is. But I just wanted to check that this sort of casting is fairly conventional.Just curious (since I'm planning on refactoring my FirstOfMonth and LastOfMonth functions to adopt this new method). By the way, this also made me realize that I could strip the timestamp from any datetime by using this: [code="sql"] dateadd(dd, datediff(dd, 0, @ThisDate), 0)[/code]Again, this is much more streamlined than my string manipulation method.So thanks for showing me the light.

RE: Last Day of any Month

Janie.Carlisle — Thu, 03 Sep 2009 07:13:30 GMT

Not sure if you're just trying to get the beginning and end of a month or the day that the month begins and ends on.CREATE FUNCTION [dbo].[dbFunc_GetFirstDayOfMonth] ( @pInputDate DATETIME )RETURNS DATETIMEBEGIN RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME)ENDCREATE FUNCTION [dbo].[dbFunc_GetLastDayOfMonth](@LoadID AS Int)RETURNS DATETIMEBEGINDECLARE @Year AS INTDECLARE @Month AS INTDECLARE @LastDay AS INTSET @Year = LEFT(@LoadID, 4)SET @Month = RIGHT(@LoadID, 2)SET @LastDay = CASE @Month WHEN 1 THEN 31 WHEN 2 THEN CASE WHEN @Year % 4 = 0 THEN 29 ELSE 28 END WHEN 3 THEN 31 WHEN 4 THEN 30 WHEN 5 THEN 31 WHEN 6 THEN 30 WHEN 7 THEN 31 WHEN 8 THEN 31 WHEN 9 THEN 30 WHEN 10 THEN 31 WHEN 11 THEN 30 WHEN 12 THEN 31END RETURN CAST(CASE WHEN LEN(@Month) = 1 THEN '0' + CAST(@Month AS CHAR(1)) ELSE CAST(@Month AS CHAR(2)) END + '/' + CAST(@LastDay AS CHAR(2)) + '/' + CAST(@Year AS CHAR(4)) AS DATETIME)END

RE: Last Day of any Month

Martin Wills — Wed, 02 Sep 2009 07:31:21 GMT

A small correction ...[code]declare @ThisDate datetime;set @ThisDate = getdate();select getdate() as CurrentDateTime, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) as BeginningOfNextMonth, -- Beginning of next month dateadd(mm, datediff(mm, 0, @ThisDate) + 1, -1) as EndOfThisMonth -- End of this month[/code]

RE: Last Day of any Month

Lynn Pettis — Tue, 01 Sep 2009 06:28:27 GMT

Much simplier:[code]declare @ThisDate datetime;set @ThisDate = getdate();select getdate() as CurrentDateTime, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) as BeginningOfNextMonth, -- Beginning of next month dateadd(mm, datediff(mm, 0, @ThisDate) + 1, -1) as BeginningOfNextMonth -- End of this month[/code]You can find some more date routine here [url=http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx][b]Some Common Date Routines[/b][/url]

RE: Last Day of any Month

akilamm — Tue, 01 Sep 2009 05:23:13 GMT

Hi Folks,Try this to get get last day of monthCREATE FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @pInputDate DATETIME )RETURNS DATETIMEBEGIN DECLARE @vOutputDate DATETIME SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) - (DAY(@pInputDate) - 1) AS DATETIME) SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate)) RETURN @vOutputDateENDGO

RE: Last Day of any Month

ps. — Wed, 26 Aug 2009 01:03:07 GMT

[code] SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),Casedatepart(dw, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))When 0 Then 'Sunday'when 1 then 'Monday'When 2 then 'Tuesday'when 3 Then 'Wednesday'When 4 Then 'Friday'When 5 Then 'Saturday'End as Day[/code]Refer - [url] http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/[/url]

Last Day of any Month

lefrancisco1 — Wed, 26 Aug 2009 00:18:50 GMT

Hi Folks,How to get the Last Day of any month, .bUT FOR EVERY MONTH I SHOULD GETLast DAYEXAMPLE; LastDay of the month August is 31 with their datename(week day) LastDay of the month February is 28 with their datename(week day) ...likewise