checking for smalldatetime column equal to GetDate() - ignoring time

I have a column of smalldatetime type, date

I'd like to run a query that only retrieves rows:

where date = convert(smalldatetime,开发者_如何学PythonGetDate())

However, this is never finding matches as it is also comparing the times.

Ie: 01-01-2010 12:23:00 != 01-01-2010 12:25:00

How can I find matches on only the date portion?

One way which will utilize the index

where date >= dateadd(dd, datediff(dd, 0, getdate()), 0)
and date < dateadd(dd, datediff(dd, 0, getdate()), +1)

See also: How Does Between Work With Dates In SQL Server?

Try:

where datediff(dd, yourdate, GetDate()) = 0

Convert your DateTime values to Date values. That will store only the date portion for you to compare.

A list of available Date and Time datatypes in MSSQL is here.

You could try:

Where CAST(FLOOR(CAST([Date] AS FLOAT)) AS DATETIME) = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

This would make both dates appear as:

2010-08-05 00:00:00.000

and thus only comparing the dates ignoring the times

found this:

CAST(CONVERT (CHAR(8), GETDATE(), 112) AS smalldatetime)

from here: reference

this sql worked for me when I added a smalldatetime field to an existing table:

SELECT     testing 
FROM         LTCCAssessment
WHERE     (testing = CAST(CONVERT(CHAR(8), GETDATE(), 112) AS smalldatetime))

where cast(CONVERT(char(10),DATETIME_FIELD_1,101) as datetime) 
  = cast(CONVERT(char(10),DATETIME_FIELD_2,101) as datetime)