CODEWITHSUNDEEP
sql
Sam
Sam

Reputation: 5270

What is wrong with sql query

What is wrong with this sql query. I am getting syntax error near convert

"SELECT * FROM collections c where c.submittedBy='679' AND CONVERT(varchar, c.eventTime, 103) ='2017-06-21'";

The eventTime is "eventTime": "2017-06-21T12:20:03.9366135+05:30"

The error is

{"Message: {\"errors\":[{\"severity\":\"Error\",\"location\":{\"start\":64,\"end\":71},\"code\":\"SC1001\",\"message\":\"Syntax error, incorrect syntax near 'CONVERT'.\"}]}\r\nActivityId: a0c51c53-fca0-4c81-8f10-65348749e3d2"}

Upvotes: 0

Views: 223

Answers (2)

MtwStark
MtwStark

Reputation: 4058

convert to date before to convert to varchar

SELECT * 
FROM collections c 
where c.submittedBy='679' 
AND CONVERT(varchar(10), CONVERT(date, c.eventTime, 103)) = '2017-06-21'

if eventTime is already a datetime field should work also this way..

SELECT * 
FROM collections c 
where c.submittedBy='679' 
AND CONVERT(date, c.eventTime) = '2017-06-21'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271091

The best way to write this query (across databases) is:

SELECT c.*
FROM collections c 
WHERE c.submittedBy='679' AND 
      c.eventTime >= '2017-06-21' AND
      c.eventTime < '2017-06-22';

This allows the database to use an appropriate index for filtering. Note that if submittedBy is a number, then don't use single quotes.

Upvotes: 4

Related Questions