for a basic SQL command via PHPMyAdmin in MySQL I want to select items more than 1 day old. I'm getting a basic error saying that there's a SQL syntax error, but can't tell what I'm doing wrong here:
COMMAND:
select *
from table_name
where column_name < Date_Add(day, -1, GetDate())
and user_id = 1
and column_name <> '0000-00-00 00:00:00'
ERROR:
#1064 - You have an error in your SQL syntax; check the manual for the right syntax to use near '-1, GetDate开发者_JAVA百科()) and user_id = 1 and column_name <> '0000-00-00 00:00:00' LIMI' at line 3
Any clues? Thx!!
I was thinking the syntax here is DATE_ADD.
Ok here is from the 5.0 reference guide:
The DATE_ADD() function and its synonym ADDDATE() allow you to add or subtract an interval to the selected date, date function or date constant. DATE_SUB() and SUBDATE() work in the same way but the interval specified is subtracted. (If the interval was negatvie DATE_SUB() makes it positive).
mysql> SELECT NOW(), DATE_ADD(NOW(), INTERVAL 1 MONTH) \G
*************************** 1. row ***************************
NOW(): 2008-09-25 11:43:29
DATE_ADD(NOW(), INTERVAL 1 MONTH): 2008-10-25 11:43:29
1 row in set (0.00 sec)
Try using NOW() for the error.
Some engines (i.e. tsql) use DateAdd()... MySql uses Date_Add() - your missing an underscore.
Try
Date_Add(CurDate(), INTERVAL -1 DAY)
I voted up NgM's response ABOVE for his idea to use NOW() - here's the final version that worked:
select *
from table_name
where column_name < DATE_ADD(NOW(), INTERVAL -1 DAY)
and user_id = 1
and column_name <> '0000-00-00 00:00:00'
Isnt it
Date_Add(....)
or
AddDate(...)
精彩评论