Aleks
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?
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()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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.
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.)
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!
ASKER