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

Question

I have a column of smalldatetime type, date

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

where date = convert(smalldatetime,GetDate())

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?

Answer 1

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?

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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))

Answer 6

Try:

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