CURRENT_TIMESTAMP vs. CURRENT_DATE

[zenweezil]

Any ideas why CURRENT_TIMESTAMP works perfectly in my SQL command but CURRENT_DATE causes a syntax error?

Here is the working SQL:
rsEvents.Source = "SELECT * FROM dbo.T_Events WHERE CONVERT(DATETIME, enddate) > CURRENT_TIMESTAMP"

This works if enddate is 10/19/2005 (yesterday) or older - but I actually want the endate to be >= to today's date - but just changing the operator from > to >= doesn't work - I am assuming because there is no time portion to enddate thus doesn't equal the current_timestamp.

But changing CURRENT_TIMESTAMP to CURRENT_DATE returns a syntax error.

It even creates a syntax error when making a direct comparison like this: WHERE CURRENT_DATE = '10/20/2005'

Any ideas why CURRENT_DATE is treated differently - I thought CURRENT_DATE is supposed to return mm/dd/yyyy and that is the format of my database entry - 10/20/2005.

[felgall]

What is the name of the 20th month of the year? I always thought that there were only 12.

[zenweezil]

ok, I meant to say "CURRENT_DATE is supposed to return mm/dd/yyyy"

Now that the typo is fixed, do you have any helpful suggestions?

Or are you trying ot say I have the default format for CURRENT_DATE wrong?

[Roelf]

what is the datatype of the field "enddate"?

[zenweezil]

endate is varchar 255 - with a value of 10/20/2005

That is why I was converting before comparing:
CONVERT(DATETIME, enddate)

[Roelf]

so, can't you do something with the getdate() function you learned to use in the other thread? :rolleyes:

Like
SELECT * FROM dbo.T_Events WHERE CONVERT(DATETIME, enddate) >= getdate()

does that work?