We help IT Professionals succeed at work.

compare date to getdate()

Aleks
Aleks asked
on
277 Views
Last Modified: 2018-01-17
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

Comment
Watch Question

Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Great!  thank you!.
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Thanks. I updated my query accordingly.  out of curiosity why is it a bad ?
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
got it  thx
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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.)
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
Yes, forgot that. Just as Where 1 = 1 is ignored. Thank you!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.