'EOMONTH function in Microsoft SQL did not return records in December 31

I was doing a question that required me to show all orders made on the last day of the month and order by EmployeeID and OrderID. When I used EOMONTH, the result did not include records on December 31, 2015. Would be glad if someone can help me point out the problem here.

SELECT EmployeeID, OrderID, OrderDate 
FROM Northwind.dbo.Orders
WHERE OrderDate = EOMONTH(OrderDate)

The two missing records were:

    EmployeeID      OrderID            OrderDate
    3                10806             2015-12-31 11:00:00.000
    4                10807             2015-12-31 11:00:00.000

Here is the link to the SQL file: https://drive.google.com/file/d/1nMo8DMqHrhV67gqkyERA_YsWbIhZxjrk/view?usp=sharing

Thanks in advance!

sqlsql-server


Solution 1:[1]

EOMonth returns date value and in the where clause the condition is between datetime vs date. So modify the query as below.

SELECT EmployeeID, OrderID, OrderDate 
FROM Northwind.dbo.Orders
WHERE cast(OrderDate as date)= EOMONTH(OrderDate)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Srishuk Kumar Bhagwat

Related Questions