EOMONTH function gives wrong result

Question

When using the EOMONTH function on a datetime datatype field the result is incorrect in my opinion.

Declare @endofmonth as datetime
Declare @startofmonth as datetime
Set @startofmonth = datefromparts(2021,5,1)
Set @endofmonth = EOMONTH (@startofmonth)
print @endofmonth

Result: May 31 2021 12:00AM
However result should be: May 31 2021 11:59PM
It is easily to avoid by choosing a Date in stead of a Datetime datatype, but still....

Answer 1

Hi @Beverdam, Frank ,

Welcome to Microsoft Q&A!

As mentioned by other experts, EOMONTH is one function which returns the last day of the month containing a specified date, with an optional offset.

So EOMONTH (@startofmonth) could report one date instead of datetime.

When we convert between date and datetime, the time component is set to 00:00:00.000. This is because the date value doesn’t contain any time information.

For example:

select  cast('2021-05-31' as datetime)  
--2021-05-31 00:00:00.000  
  
print cast('2021-05-31' as datetime)  
--May 31 2021 12:00AM  

If you would like to output like 'May 31 2021 11:59PM', you could have a try with below:

Declare @endofmonth as datetime  
Declare @startofmonth as datetime  
Set @startofmonth = datefromparts(2021,5,1)  
Set @endofmonth = DATEADD(SECOND,60*60*24-1,cast(EOMONTH (@startofmonth) as datetime))  
print @endofmonth  

Output:

May 31 2021 11:59PM  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Beverdam, Frank ,

As the official documentation says it here: eomonth-transact-sql

Return Type: date

Though you are using a different data type, i.e. datetime. That's why an implicit casting is happening behind the scene.

SQL

DECLARE @endofmonth DATE  
 , @startofmonth DATETIME = GETDATE();  
  
SET @endofmonth = EOMONTH (@startofmonth);  
SELECT @endofmonth AS EOM;