CODEWITHSUNDEEP
sqlsql-server
level_0_apprentice
level_0_apprentice

Reputation: 17

How do I compare smalldatetime in SQL?

I'm trying to select only rows that occurred within the last 12 hours. The below query works fine without the Where clause, I get exactly what I want for the "Called_At" column. But when I add the where clause it gives me the error "Conversion failed when converting character string to smalldatetime data type."

The query is

SELECT TOP (2000) 
      CONVERT(smalldatetime, CONCAT([Call_Date],' ',[Call_Time])) As 'Called_At'
      ,[Other_Column1]
      ,[Other_Column2]
      ,[etc]

  FROM [Database].[dbo].[Table]
  WHERE CONVERT(smalldatetime, CONCAT([Call_Date],' ',[Call_Time])) >= DATEADD(hour,-12,GetDATE())
  ORDER BY [Call_Date] DESC, [Call_Time] DESC

Here is a sample of the columns without the Where part of the statement. 

Called_At             Column1       Column2
-------------------   -------       ---------
2020-04-05 23:09:00   Data          MoreData    
2020-04-05 20:50:00   Data          MoreData
2020-04-05 17:08:00   Data          MoreData
2020-04-05 16:49:00   Data          MoreData

Why does the first CONVERT() statement work fine but the second one doesn't?

Upvotes: 0

Views: 411

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

This is because of the order of operations. I would suggest that you find the offensive values using:

SELECT CONCAT([Call_Date], ' ', [Call_Time]))
FROM [Database].[dbo].[Table]
WHERE TRY_CONVERT(smalldatetime, CONCAT([Call_Date], ' ', [Call_Time])) IS NULL

Once you see what the data looks like, you can figure outhow to fix it.

Upvotes: 1

Related Questions