Issue with a select query (Pyodbc)

Question

I am using Pyodbc to access a Microsoft SQL Server database. I have the following query being built in code:

SELECT created, transactionID, balanceEffect
FROM dbo.table
WHERE (SELECT YEAR(created) = 2020) 
 AND (SELECT MONTH(created) = 11) 
 AND (SELECT DAY(created) = 17) 
 AND balanceEffect > 0 
 AND TransactionTypeID = '1321837129837129213' 
ORDER BY created

Where created is a DATE column.

When it gets to executing the query I see the following error:

ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '='. (102) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '='. (102); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '='. (102)")

It seems like there is a problem with the subquery stuff. I haven't been able to find a similar issue online, and I believe this code (or a very similar piece of code) was working previously in this project. Does anyone have any idea what might be causing it?

• Barmar: You don't need the subqueries. Just WHERE YEAR(created) == 2020 AND ...

Answer 1

The SELECTs are not correct. You just want:

WHERE YEAR(created) = 2020 AND 
 MONTH(created) = 11
 DAY(created) = 17 AND
 balanceEffect > 0 AND
 TransactionTypeID = '1321837129837129213' 

Or more simply:

WHERE CAST(created as DATE) = '2020-11-17' AND
 balanceEffect > 0 AND
 TransactionTypeID = '1321837129837129213' 

• user2518712: Hi, thanks for your reply. This is what I needed, much appreciated. Don't know why I thought I needed to select, fresh pair of eyes saved the day cheers

Answer 2

The subqueries make no sense, and are not needed. Just check created against literal dates, like this:

SELECT created, transactionID, balanceEffect
FROM dbo.table
WHERE 
 created >= '20201107' and created < '20201108'
 AND balanceEffect > 0 
 AND TransactionTypeID = '1321837129837129213' 
ORDER BY created

You might want to simplify the date filtering conditions as:

CONVERT(date, created) = '20201107'

However this is less efficient: the whole created column needs to be converted to a date before it can be filtered; this cannot take advantage of an index. This is why I recommend the half-open interval filtering, as shown in the first query.

Of course, if created has no time component, then you would just do:

created = '20201107'

• user2518712: Thank you for your reply very helpful info. Oversights by me