Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Last day of the month

Author  Topic 

kwacz23
Starting Member

44 Posts

Posted - 2014-03-09 : 05:57:22
Hi

I would like to find all orders which finish in the last day of the month

select orderid,orderdate,custid,empid from Sales.Orders
where convert (date,orderdate,120) in (convert(date,(SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,Sales.Orders.orderdate)+1,0)) from Sales.Orders),120))

Unfortunately this query return error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Pls help

k_a_f_k_a
Starting Member

2 Posts

Posted - 2014-03-09 : 06:37:51
I think the error message is quite clear: The subquery is returning more than 1 row, which is then being passed as an expression towards convert().

Also, this looks needlessly complex. I guess this should work:
     select orderid,orderdate,custid,empid from Sales.Orders
where datepart(day, dateadd(day, 1, orderdate)) = 1
Basically, add 1 day and check if this is the first day of any month.
Go to Top of Page
   

- Advertisement -