Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: compare datetime day with GETDATE()

I have a stored procedure that should fetch all records with a date equal to the current date or in the future. The dates are saved in column targetDate and formatted as datetime. My corresponding WHERE clause is the following:

WHERE A.targetDate >= GETDATE()

In general my stored procedure works fine, my only problem is if the targetDate equals the current date as all dates are saved as follows, i.e. with the time set to zeros:

2014-02-22 00:00:00.000

How do I have to change my WHERE clause so that it only considers the date but ignores the time saved with it so that I get any records with the current date even if the time is already passed ?

Many thanks for any help with this, Tim.

like image 750
user2571510 Avatar asked Feb 22 '14 18:02

user2571510


People also ask

How can I compare two dates in SQL Server?

Here we will see, SQL Query to compare two dates. This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.

Can we compare date with datetime in SQL?

The right way to compare date only values with a DateTime column is by using <= and > condition. This will ensure that you will get rows where date starts from midnight and ends before midnight e.g. dates starting with '00:00:00.000' and ends at "59:59:59.999".


2 Answers

Change to:

WHERE A.targetDate >= cast(GETDATE() as date)

Edit - because targetdate also contains time, yes, format both like this:

WHERE cast(A.targetDate as date) >= cast(GETDATE() as date)

Edit - given comments re: performance, may want to try:

WHERE a.targetdate >= cast(cast(getdate() as date) as datetime)

Last edit should give you the same result and take advantage of any indexes on targetdate

like image 59
Brian DeMilia Avatar answered Sep 23 '22 05:09

Brian DeMilia


The following should give you the current date with no time:

SELECT DATEADD(dd,0,DATEDIFF(dd,0,GETDATE()))

This should be your final line:

WHERE A.targetDate >= DATEADD(dd,0, ATEDIFF(dd,0,GETDATE()))
like image 37
Preli Avatar answered Sep 22 '22 05:09

Preli