In SQL Server Denali, seven new datetime functions have been introduced, namely,
- DATEFROMPARTS (year, month, day)
- DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
- DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
- DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
- SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
- TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
- EOMONTH (start_date)
EOMONTH() is a very interesting function. It is a very common requirement in many major applications where the user needs the last day of the month. It is very easy to figure out what is the first day of the month because obviously, it is comes first anyway. However, the last day of the month keeps changing as every month has different number of days and leap year also makes this more interesting.
Let us understand this function using few examples.
Example 1: Use of EOMONTH() function to find last day of the month
SELECT EOMONTH(GETDATE()) LastDayofMonth
Example 2: Use of EOMONTH() function to find last day of the month during Leapyear
SELECT EOMONTH('20110201') NonLeapYearFebLastDay;
SELECT EOMONTH('20120201') LeapYearFebLastDay;
SELECT EOMONTH('20130201') NonLeapYearFebLastDay;
Example 3: Use of EOMONTH() function to find last day Previous and Next Month
SELECT EOMONTH(GETDATE(),-1) PreviousMonthLastDay;
SELECT EOMONTH(GETDATE(),1) NextMonthLastDay;
Example 4: Use of EOMONTH() function to find last day Previous and Next Month
SELECT DATENAME(dw,EOMONTH(GETDATE())) LastDayofMonthDay;
Example 5: Get First Date of Next Week
SELECT DATEADD(d,1,EOMONTH(GETDATE())) NextMonthFirstDay;
Have you tried the following method? I personally use the method mentioned above but once in an interview, one of the candidates gave the following answer, and I found it very interesting.
SELECT REPLACE(EOMONTH(GETDATE(),1),31,1) NextMonthFirstDay;
Well, you can also apply this function to any table column to get the desired value. I have previously written an UDF when I had the following requirement: when any user registers in an application, he should get automatically validated till the end of the month. I will now replace that custom logic/UDF with this function.
Quick Video on the same subject
Reference:Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Wow this would have come in handy a couple of months ago :)
Is there any eta on the final version for Denali?
Best regards, and keep up the great work!
It will work on Denali only.
Three beers and using an android tablet, but this should be close to an end of month function , no denali required
Create function fnEOM(@d datetime) returns datetime
As
Return
Dateadd(day,-1,Dateadd(month,1,Dateadd(day,-Datepart(day,@d)+1,@d
Should strip time as well, I will leave as exercise :-)
This is simple
select dateadd(month,datediff(month,0,getdate())+1,-1)
Wow! Thanks a lot sir. Great Article.
Hi Pinal,
This is a great post for EOMonth() in SQL Server – Denali, As I’ve a ??? on example 5 with Replace(). I believe which isn’t a dynamic way to find NextMonthofFirstDay, what if you have 03/31/2011 instead of getdate(), then EOMonth() will return 04/30/2011 & then you don’t have any replacement for 30, right?
Does this make perfect sense to you or not ??
Many ways to “skin a cat”
— function
ALTER FUNCTION fn_EOMONTH
(
@MyDate datetime
)
RETURNS DATETIME
AS
BEGIN
SET @MyDate = dateadd(m,1,@MyDate)
RETURN ( @MyDate-datepart(d,@MyDate) )
END
— function call example
SELECT dbo.fn_EOMONTH(’25/10/2011′)