开发者

SQL - Select entries more than 1 day old. error #1305 Function doesn't exist

开发者 https://www.devze.com 2023-03-13 15:14 出处:网络
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 he

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 &lt;&gt; '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(...)
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号