Last day of the month - SQL Server Forums

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources