datetime stamps 'causing syntax errors?

[]|V|[agnus]

at first i thought my problem had to do with a join i was attempting, but i found out that i stop getting the "syntax error or access violation" message from ColdFusion when i remove the variable references to a date object. now, this is obviously not a general CF shortcoming. i use dates in queries and otherwise all the time. i am wondering if anybody can spy anything, perhaps use of reserved words in SQL, that might be 'causing me guff.

here is a sample query:

SELECT * FROM auctions,auctions_bids WHERE (auctions.id = auctions_bids.auction) AND (auctions.end > {ts '2005-05-03 15:04:48'}) ORDER BY auctions.end DESC

"{ts '2005-05-03 15:04:48'}" is what is returned from the CF function to create an ODBCDateTime formatted value. if i change that query to:

SELECT * FROM auctions,auctions_bids WHERE (auctions.id = auctions_bids.auction) ORDER BY auctions.end DESC

the error goes away.

:confused: :confused: :confused:

[Tangerine Dream]

SELECT * FROM auctions,auctions_bids WHERE (auctions.id = auctions_bids.auction) ORDER BY auctions.end DESC
Hi, if @date variable holds date in '2005-05-03 15:04:48' format, you should try syntax like:



SELECT * FROM auctions,auctions_bids
WHERE (auctions.id = auctions_bids.auction) AND (DATEDIFF(auctions.end, @date) > 0)
ORDER BY auctions.end DESC;

-- which means:
SELECT * FROM auctions,auctions_bids
WHERE (auctions.id = auctions_bids.auction) AND (DATEDIFF(auctions.end, '2005-05-03 15:04:48') > 0)
ORDER BY auctions.end DESC;


You should also use quoted variable value (like '$date'), if necessary

[]|V|[agnus]

I will try that SQL function, thanks. For the record though, at least for ColdFusion, quotes are required around string values only. Numeric, boolean and datetime values don't require quotes and in fact require them not to be there.

[Tangerine Dream]

Quotes for datetime/timestamp values required by MySQL server, e.g to insert or use as MySQL function argument timestamp value such as 2005-05-03 15:04:48, it must be surrounded by single quotes, i.e '2005-05-03 15:04:48'