SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Last Day of any Month


Last Day of any Month

Author
Message
zubamark
zubamark
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 71
Try this

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39922 Visits: 38564
Here is some code for you all to play with. Use at your own risk. I tested this with my Tally table.


create function dbo.tvf_EOM (
@iDate datetime
)
returns table
as
return select
dateadd(mm, datediff(mm, 0, @iDate) + 1, -1) as EndOfThisMonth,
datename(dw,dateadd(mm, datediff(mm, 0, @iDate) + 1, -1)) as DatenameEndOfThisMonth;
go

select
dateadd(dd, t.N - 1, 0) as TheDate,
EndOfThisMonth,
DatenameEndOfThisMonth
from
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;



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sreid08
sreid08
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 50
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/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87424 Visits: 41113
ps (8/26/2009)
 SELECT  DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),
Case
datepart(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



Refer - http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/


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.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87424 Visits: 41113
lefrancisco1 (8/26/2009)
Hi Folks,

How to get the Last Day of any month, .bUT FOR EVERY MONTH I SHOULD GETLast DAY

EXAMPLE; 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


Ummmm.... which year?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87424 Visits: 41113
sreid08 (9/4/2009)
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/


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.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87424 Visits: 41113
Steve Thompson (9/3/2009)
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?


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

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87424 Visits: 41113
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...
--===== 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)



... and here's a function made from the efficient method Michael Valentine Jones made for finding the last day of the month...
 CREATE FUNCTION dbo.LastDayOfMonth (@AnyDate DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(mm,DATEDIFF(mm,-1,@AnyDate),-1)
END



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

    SET NOCOUNT ON
DECLARE @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



Last, but not least, here's the results from my humble desktop...


========== 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.



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

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5820 Visits: 11771
Here is another series of tests showing the difference in performance of inline code vs. scalar UDFs.
Demo Performance Penalty of User Defined Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78601


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 Periods
One 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:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

Finding the End of Time Periods
Sometimes 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:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
End of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760


For a collection of other links related to datetime in SQL Server, see this:
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
sreid08
sreid08
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 50
Jeff Moden (9/4/2009)
sreid08 (9/4/2009)
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/


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.



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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search