WHERE Date >= getdate()

vbnetcoder
vbnetcoder used Ask the Experts™
on
I have a query where i am trying to get any record were the date is either today or greater than today

This is my query

SELECT * FROM Table
WHERE EndDate >= getdate()

today's date is 2/29/2016 and it is not returning any records that are like this 2016-02-29 00:00:00.0000000

I suspect it is because of the time portion? how do i remove the time out of the equation.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Numerous ways but I use this

SELECT * FROM Table
WHERE EndDate >= cast(convert(nvarchar(10), getdate(), 103) as datetime)

Open in new window


You might need to alter the 103 to 101 depending on your date settings.
You can also use this:

SELECT * FROM Table
WHERE EndDate >= cast(getdate() as date)

Open in new window

Hi coder,

It's probably due to the way things are being recast implicitly.  If you'll explicitly cast the value to the appropriate type you can get past it.

SELECT * FROM Table
WHERE cast (EndDate as date) >= getdate()

  or

SELECT * FROM Table
WHERE EndDate >= cast (getdate() as {date type of EndDate})

If you have a lot of data, the second will be faster as the recast occurs on the static data, not the row data.  However, if the EndDate column is a string in 'mm/dd/yyyy' format you'll need to use the first form as a date string doesn't compare easily unless it's in 'yyyy-mm-dd' format.


Good Luck!
Kent
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Commented:
Are you storing future dates in EndDate in your table?

If not, do you want to check this?

SELECT * FROM Table
WHERE EndDate =< getdate()

Open in new window

awking00Information Technology Specialist

Commented:
What data type is end_date?

Author

Commented:
ty

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