walkman walkman - 2 months ago 18
SQL Question

SQL SELECT not returning value

I have the following query in SQL:

SELECT idPass
FROM Notes
WHERE CAST(CreatedDate AS nvarchar) LIKE '2016-07-30 17:19:10%'
AND CreatedBy='César Caldeira - CEC'
AND idUser='7'


I'm a noob with SQL; this has no syntax errors but I don't know how to make it work?

Any advice?

This is my table structure:

idPass int
CreatedDate datetime
CreatedBy nvarchar(50)
idUser int


The problem is datetime has the format
yyyy-mm-ss.mmm
and I don't have access to the last 3 digits of the date so I can't use
CreatedDate = somedate

Answer

Here is a sql-server answer that will strip off the milliseconds (last 3 digits) and compare as dates.

SELECT idPass
FROM Notes
WHERE DATEADD(ms,- DATEPART(ms,CreatedDate),CreatedDate) = CAST('2016-07-30 17:19:10' AS DATETIME)
AND CreatedBy='César Caldeira - CEC'
AND idUser='7'

Here is a SQL Server 2012+ answer that uses FORMAT to drop off milliseconds and compare as strings.

SELECT idPass
FROM Notes
WHERE FORMAT(CreatedDate ,'yyyy-MM-dd HH:mm:ss') = '2016-07-30 17:19:10'
AND CreatedBy='César Caldeira - CEC'
AND idUser='7'