>= GETDATE() not returning rows with today's date

4.2k views Asked by At
---------------------
| ID  |     date    |
|-----+-------------|
|  1  |  2013-12-30 |
|  2  |  2014-01-03 |
|  3  |  2014-02-02 |
---------------------

column ID is int type

column date is Date type

When i use SELECT CAST (getdate() as date) currentDay, ID FROM table WHERE date >= GETDATE(), it only returns:

--------------------------------------
|  currentDay   |  ID  |     date    |
|---------------+------+-------------|
| 2013-12-30    |   2  |  2014-01-03 |
| 2013-12-30    |   3  |  2014-02-02 |
--------------------------------------

it does not return the row with ID=1.

In other words, >=GETDATE() does not return rows with today's date.

What did I do wrongly?

2

There are 2 answers

0
Lamak On BEST ANSWER

It doesn't return any rows with today's date because GETDATE() gives date and time. So, in your example, converting 2013-12-30 to a DATETIME gives you 2013-12-30 00:00:00, which isn't equal or greater than 2013-12-30 12:09:35 (the result of GETDATE() for me in this moment). You need to use:

WHERE date >= CONVERT(DATE,GETDATE())

0
peter.petrov On

The GETDATE() function returns the current date and time (i.e. including the time part). So it is normal that today's date (without the time part) is smaller than GETDATE() ( unless you run your query at midnight precisely ). This is why you don't get the expected result.