Cao, Wenhong wrote:
I am trying to select the records where the field activationtimestamp is
within a certain range. Unfortunately the field activationtimestamp is
defined as character(14) in the table.
Perhaps you should change the type of activationtimestamp:
ALTER TABLE SubscriptionVersion CHANGE activationtimestamp activationtimestamp DATETIME;
mysql> select * from SoutheastDB.SubscriptionVersion where
date(activationtimestamp) > DATE_ADD('20040618070000', INTERVAL 5 HOUR)
and activationtimestamp <= '20040619065959' limit 1;
ERROR 1064: You have an error in your SQL syntax near
'(activationtimestamp) > DATE_ADD('20040618070000', INTERVAL 5 HOUR) and
activati' at line 1
The DATE() function was added in version 4.1.1. From the error message, I expect you're using an earlier version. In any case, DATE() doesn't do what you want. It extracts the date portion of the input, throwing away the time part. Not very useful when you want to compare to a DATETIME.
It seems to me it would be just as easy to type '20040618120000' as DATE_ADD('20040618070000', INTERVAL 5 HOUR).
SELECT * from SoutheastDB.SubscriptionVersion WHERE activationtimestamp > '20040618120000' AND activationtimestamp <= '20040619065959' LIMIT 1;
If you need the DATE_ADD, then you need to reformat the result to match your char(14) activationtimestamp column:
SELECT * FROM SoutheastDB.SubscriptionVersion
WHERE activationtimestamp >
DATE_FORMAT(DATE_ADD('20040618070000',INTERVAL 5 HOUR), '%Y%m%d%H%i%s')
AND activationtimestamp <= '20040619065959'
LIMIT 1;On the other hand, if you change activationtimestamp to a DATETIME, you could simply
SELECT * FROM SoutheastDB.SubscriptionVersion
WHERE activationtimestamp > DATE_ADD('20040618070000',INTERVAL 5 HOUR)
AND activationtimestamp <= '20040619065959'
LIMIT 1;Thanks,
Wen
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
