question

BeverdamFrank-5678 avatar image
0 Votes"
BeverdamFrank-5678 asked MelissaMa-msft edited

EOMONTH function gives wrong result

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

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If the expectation is that the end of the month is the last possible time in that month - then your expected answer is also incorrect. That time would be '2021-05-31 23:59:59.997' for a datetime data type - and a different value for a datetime2 data type.

The EOMONTH function returns a date data type - and your code is converting that to a datetime data type implicitly. That implicit conversion is going to set the time portion to 00:00:00.000.

Since we don't actually need to set the value to an actual end date with time, I don't see how this has any impact. When querying for a date range you should be using an open-interval range anyways which would be: WHERE datecolumn >= @startofmonth AND datecolumn < dateadd(day, 1, @endofmonth) - where @endofmonth has 00:00:00.000 as the time.

0 Votes 0 ·

Hi BeverdamFrank-5678,

Could you please validate all the answers so far and provide any update?

If all are not working or helpful, please provide more details.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @BeverdamFrank-5678,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @BeverdamFrank-5678,

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;


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.