CODEWITHSUNDEEP
sqlsql-server
Dusty
Dusty

Reputation: 173

MSSQL query returns no results

the query:

SELECT sv_inquiry.inquiry_id, sv_inquiry.owner_rep, sv_inquiry.status
      ,sv_inquiry.owner_rep, sv_inquiry.closed_dt 
FROM sv_inquiry 
WHERE sv_inquiry.typ = 'incident' 
     AND sv_inquiry.status = 'resolved' 
     AND sv_inquiry.owner_grp = 'service center NL & INT' 
     AND sv_inquiry.closed_dt LIKE '%17-6-2013%' 
ORDER BY sv_inquiry.inquiry_id DESC

the problem:

This query returns an empty resultset unless I leave out "AND sv_inquiry.closed_dt LIKE '%17-6-2013%'", but if I leave that out I get all the results from the last couple of years and I only need to have the results from a specific date.

sv_inquiry.closed_dt is a field of the DATETIME type.

I should probably implement the use of CAST or CONVERT, although I don't know how to adjust the query to include that and only show results of a specific date.

(the query is used on PHP site with SQLSRV_QUERY statement)

Can anyone adjust the query for me so I can search a specific date with the sv_inquiry.closed_dt field?

Upvotes: 1

Views: 1031

Answers (5)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131227

You are trying to compare a datetime value with a string literal. To do that, SQL Server must either convert the datetime value to a string using the column's collation, or convert the string to a datetime, if it can determine its format. '17-6-2013' is not a recognizable format so SQL Server converts the dt_closed value to string before comparing.

'2013-6-17' is not a safe option either. It is one of the formats that are not affected by the DATEFORMAT property, but it is still affected by the collation.

The only safe format is '20130617', the unseparated ISO 8601 format.

You can bypass the entire issue of conversion by using a parameterized SQL statement and pass the filter value as a date parameter.

Another issue is that applying any sort of function to the column dt_closed will cause the query optimizer to ignore any indexes defined on dt_closed and force a table scan.

A better solution is to convert the equality operation to a range query using BETWEEN eg.

AND sv_inquiry.closed_time between '20130617' AND '20130618'

This will avoid conversions to string and utilize any underlying indexes

Upvotes: 1

juergen d
juergen d

Reputation: 204756

Replace

AND sv_inquiry.closed_dt LIKE '%17-6-2013%'

with

AND DATEADD(dd, 0, DATEDIFF(dd, 0, sv_inquiry.closed_dt)) = '2013-06-17'

SQLFiddle demo

To overcome the problem that this won't use indexes I suggest you split the datetime column in to seperate columns: date and time like sv_inquiry.closed_date and sv_inquiry.closed_time.

Upvotes: 1

Sunil Nepali
Sunil Nepali

Reputation: 62

 SELECT sv_inquiry.inquiry_id, sv_inquiry.owner_rep, sv_inquiry.status
  ,sv_inquiry.owner_rep, sv_inquiry.closed_dt 
 FROM sv_inquiry 
  WHERE sv_inquiry.typ = 'incident' 
 AND sv_inquiry.status = 'resolved' 
 AND sv_inquiry.owner_grp = 'service center NL & INT' 
 AND sv_inquiry.closed_dt ='2013-6-17'
 order BY sv_inquiry.inquiry_id DESC

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Just cast it to date and compare it. It is available in sql server 2008 and newer.

AND CAST(sv_inquiry.closed_dt AS DATE) = '2013-06-17'

Upvotes: 3

LuigiEdlCarno
LuigiEdlCarno

Reputation: 2415

replace

AND sv_inquiry.closed_dt LIKE '%17-6-2013%'

with

AND convert(varchar(10),sv_inquiry.closed_dt, 121) = '2013-06-17'

That will cast your datetime to a varchar using the 121format. Due to the length limitation to 10 chars, it will only return the datepart.

Alternativly, you can use

AND convert(varchar(100),sv_inquiry.closed_dt, 121) like '2013-06-17%'

if you sometimes dynamically need to check the hours and so on as well.

Upvotes: 0

Related Questions