Can I use getdate() in where filter, without time? 2

[bobpv]

Hello all,

I have a SQL database and am writing a query to return records based on a date. This is desired to be automated, so each time it runs, it looks at past date date.

Problem is the SQL data is stored without the time portion. So it's '2008-05-19 00:00:00.000'. Or course, getdate() returns the current time as well. That makes a problem, given the 24 hour clock.
If there was a way to say RequiredDate = getdate()without getdate including the time.

I have tried looking at the FAQ and searching, but cannot seem to find this one, which I am sure is pretty simple. I just seem to be working around it without getting any closer. Any help will be appreciated.

 

[bborissov]

Sure, but better first store the value into a variable and then use that variable in the query:

DECLARE @TestDate datetime
SET @TestDate = DATEADD(dd, DateDiff(dd,0,GetDate()),0)

SELECT .....
FROM....
WHERE SomeDate = @TestDate

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[ESquared]

Why is it " etter first [t] store the value into a variable and then use that variable in the query?"

SELECT .....
FROM....
WHERE SomeDate = DateDiff(dd, 0, GetDate())

That works for me. If you want you can throw Convert(datetime, <expression>) in there.

 

[bobpv]

Not sure why, but I was using the exact expression and not getting any return. Once I did the variable, it seems to work fine.

I appreciate the advice and MANY thanks.

 

[bborissov]

It should work if you use direct DATEADD(..GETDATE()...), but I prefer to use variables.
I'm just not sure how many times that function will be evaluated during the process and for huge tables if it is evaluated for every record that will slow down performance.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[ESquared]

I guarantee you that it will be evaluated one time. The query parser evaluates scalars and UDFs with scalar input early in the compilation process.

Just take a look at the execution plan and you'll see.

 

[bborissov]

Thank you! I did.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP