sql query to match dates

dpicco
dpicco used Ask the Experts™
on
Can this query be improved so I don't have to check the day, month, and year? I just want updt equal to the current date, regardless of the time.
I tried WHERE updt=getdate() but I don't get the correct result. Thanks.

	 SELECT  * 
                   FROM  dsr_journal 
                  where day(updt) = day(getdate())
                  and month(updt) = month(getdate())
                  and year(updt) = year(getdate())

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

"GetDate" also includes the current time so this alone would not help. You could cast the dates as double and use the floor function to cut the numbers after the decimal point so you could compare these dates, but the easier variant is to use the DateDiff function and set "d" as criterion and test if it is 0. Like:

...WHERE DateDiff(d,updt,GetDate()) = 0

Open in new window


Cheers,

Christian
Neil RussellTechnical Development Lead

Commented:
SELECT *
    FROM dsr_journal
        WHERE dateadd(dd,datediff(dd,0,updt),0) = dateadd(dd,datediff(dd,0,getdate()),0)

Should do the job BUt......
This involves calculations for EVERY ROW!

Author

Commented:
exactly what I needed. thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial