asked on
WHERE Date >= getdate()
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.
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.
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL
Last Comment
vbnetcoder
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
Are you storing future dates in EndDate in your table?
If not, do you want to check this?
If not, do you want to check this?
SELECT * FROM Table
WHERE EndDate =< getdate()
What data type is end_date?
ASKER
ty
Open in new window
You might need to alter the 103 to 101 depending on your date settings.