CODEWITHSUNDEEP
pythonmysql
JChao
JChao

Reputation: 2309

Python MySQL date format

I have a MySQL query written in python that checks for date range. The command is like:

"""
SELECT <something>
FROM <a_table>
WHERE datekey BETWEEN (SELECT date_key 
                       FROM `date_table` WHERE date={date_start})
              AND (SELECT date_key 
                   FROM `date_table` WHERE date={date_end})
""".format(date_start=date_start, date_end=date_end)

where date_start and date_end are variables for date strings like 2014-04-13

now when I execute this command, it seems like MySQL interpret the date string as an equation, thus returning Incorrect date value: '1997' for column 'date' at row 1

1997 = 2014 - 4 - 13

That's how I figured.

I want to pass the string in as a raw string but I've failed doing so (I tried using r'' and u'')

Is there a way to pass the variables in as string?

Upvotes: 0

Views: 1353

Answers (1)

spencer7593
spencer7593

Reputation: 108370

It seems like you are missing single quotes around the date literals.

MySQL is going to see a "bare" dash character as a subtraction operation

 SELECT 2016-04-13       --> 1999, numeric value returned by expression

MySQL is seeing the same way it would see this:

 SELECT 2016 - 4 - 13

 SELECT 2016 + -4 + -13

To get MySQL to see a literal date, we enclose it in single quotes

 SELECT '2016-04-13'                     --> a string literal (VARCHAR)

 SELECT '2016-04-13' + INTERVAL 0 DAY    --> a DATE value

 SELECT DATE('2016-04-13')               --> a DATE value returned by a function

Where used in the context that MySQL expects a DATE or DATETIME, MySQL will interpret (or implicitly convert) a string literal into the DATE or DATETIME, and will either return NULL or throw an error when the result is not a valid DATE or DATETIME value.)

MySQL will also implicitly convert numeric literal in a context where MySQL expects a DATE or DATETIME, e.g.

SELECT DATE( 20160413 )                --> DATE value

SELECT 20160413 + INTERVAL 0 DAY       --> DATE value

I don't know of anyone that relies on that. We get the same result when the literal is enclosed in single quotes...

SELECT DATE( '20160413' )              --> DATE value

SELECT '20160413' + INTERVAL 0 DAY     --> DATE value

Upvotes: 1

Related Questions