CODEWITHSUNDEEP
mysqlmysql-error-1052
steve-o
steve-o

Reputation: 67

Timestamp & Date comparison in MySQL - ambiguous error

this is a follow up to a question I got help with on here yesterday, but it's a slightly different issue - i'm trying to check if a mysql timestamp of a db record matches 7 days in the past (ignoring hours and seconds etc), but I'm getting a "Column 'import_date' in where clause is ambiguous", can anyone see the problem?

This is the date comparison: Timestamp, e.g 2010-07-13 11:04:27

WHERE date_format( `import_date`, "%Y-%m-%d" ) =
    date_format( date_sub( now(), INTERVAL 11 DAY ), "%Y-%m-%d" )

Upvotes: 2

Views: 806

Answers (1)

Martin Smith
Martin Smith

Reputation: 453028

Regarding your ambiguous error you must have 2 tables in the query both with an import_date column. You would need to use

WHERE date_format( `YourTableName`.`import_date`, "%Y-%m-%d" ) =
    date_format( date_sub( now(), INTERVAL 11 DAY ), "%Y-%m-%d" )

Just to add though that your query above will be inefficient as it is not SARGABLE

I'm not familiar enough with MySQL to know exactly what it's doing but definitely you will be better off rewriting it as

WHERE `YourTableName`.`import_date` = x

or 

WHERE `YourTableName`.`import_date` between x and y

as appropriate

Upvotes: 1

Related Questions