Check smalldatetime column equal to GetDate () - ignore time

Question

I have a column of type smalldatetime, date

I would like to run a query that only retrieves rows:

where date = convert (smalldatetime, GetDate ())

However, this never finds a match, as it is also a comparison of time.

That is: 01-01-2010 12: 23: 00! = 01-01-2010 12:25:00

How to find matches only for a part of a date?

Answer 1

One way to use the index

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

See also: How to work with dates in SQL Server?

Answer 2

Try:

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

Answer 3

DateTime Date. , .

Date Time MSSQL .

Answer 4

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

Answer 5

You can try:

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

This will result in both dates being displayed as:

2010-08-05 00: 00: 00.000

and thus only a comparison of dates ignoring times

Answer 6

found this:

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

from here: link

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