DateTime filter in SQL Server-CodePudding

I have a query

SELECT * 
FROM SAMPLETABLE 
WHERE CAST(CreatedDate AS DATETIME) =  '2020-01-01 09:00:58'

which is working for me. but I have input in yyyy-MM-dd hh:ss that is

SELECT * 
FROM SAMPLETABLE 
WHERE CAST(CreatedDate AS DATETIME) =  '2020-01-01 09:00'

which returns zero records. How to filter this data?

CodePudding user response：

When you compare datetime to datetime, the whole value (including the fraction seconds) will be compared. Equality will only give you true when both operands are really equal.

When you compare to a string, SQL Server will try to convert one of the values to the data type of the other value. This is called implicit conversion. In case of datetime and varchar, varchar will be converted to datetime (when appropriate rule is found to convert the string) due to data type precedence.

Therefore you cannot say, that any given datetime value is equal to any arbitrary string representing a date and time.

You expect to get all values in the range of 2020-01-01 09:00:00.000 and 2020-01-01 09:00:59.999, however this is not how date and time works in SQL.

If you can have such inputs, you either have to interpret them and do your search using the intervals you expect, like

SELECT * 
FROM SAMPLETABLE 
WHERE CreatedDate >= '2020-01-01 09:00:00.000'
  AND CreatedDate < '2020-01-01 09:01:00.000'

Or you can cast your datetime value to the sting format you got:

SELECT * 
FROM SAMPLETABLE 
WHERE FORMAT(CreatedDate, 'yyyy-MM-dd HH:mm') =  '2020-01-01 09:00'

The second approach is less performant in most RDBMS (SQL Server for sure) due to the conversion (calculation) done on the field itself, which will prevent the database server to use indexes on said column/to aid said filter.

Note, that you don't have to cast/convert your column to datetime if it is a datetime already.