Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

compare date to getdate()

I am trying to select reminders for 'today'
the date is saved in a datetime field, and is for example:

2017-04-18 00:00:00.000

I am trying the following but I get no results. I imagine its because it is taking the time into account. How can I compare the date only?

 
WHERE due_on= GETDATE()

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

Great!  thank you!.
Convert in WHERE clause is big bad so, you may wish to do something as follows:

declare @today [datetime] = convert([datetime], convert([date], getDate()))

where [due_on] = @today

Open in new window

Avatar of Aleks

ASKER

Thanks. I updated my query accordingly.  out of curiosity why is it a bad ?
First it happens for each row in the table "on the fly" which has performance implications.

Second, if you have an index on column due_on, it will not be used if either side of equation is using ANY function in WHERE clause and convert is a function.

Depending on number of rows in your table, you may wish to have a index on that column.

In addition, the convert on getdate() will always return the identical value, so no point calling n number of times where n is number of rows in table.
Avatar of Aleks

ASKER

got it  thx
Purely as an FYI:

WHERE convert([date], due_on) = CONVERT([Date], GETDATE())

This would occur on every row:

            convert([date], due_on)

and it is not necessary


This does not occur on every row

           CONVERT([Date], GETDATE())

(The optimizer know it only needs to perform this once.)
Yes, forgot that. Just as Where 1 = 1 is ignored. Thank you!