0

I'm practically newbie with MySQL.
I'm trying to do a query to retrieve a preformatted date, obtained from a subtraction, using CONVERT.
This is the query:

sql = f"SELECT CONVERT(timestamp - {sqlToday}, getdate(), 23), count_issue FROM total_unresolved_issue WHERE project = '{project}'"

timestamp is a column of the database, and sqlToday is a date in format datetime.date(2020, 2, 13).
Unfortunately this error returns, and I don't know how to handle it:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'getdate(), 23), count_issue FROM total_unresolved_issue WHERE project = 'Graphic' at line 1

Thanks in advance!

Improve this question
6
  • The CONVERT syntax you have here isn't mysql. What do you want this expression to return? NOW() is the current date (and other date functions in the link).
    – danblack
    Commented Apr 22, 2020 at 8:54
  • I have tried NOW(). It works very well, thanks @danblack! I found the convert documentation here. But at this point I believe it is totally incomplete.
    – Memmo
    Commented Apr 22, 2020 at 9:11
  • 1
    your link is for SQL-Server. Quite different product. Some SQL is the same, some different. There's enough MySQL documentation to keep you from guessing what is/isn't correct. The good thing about MySQL/MariaDB errors is they point exactly where the problem is.
    – danblack
    Commented Apr 22, 2020 at 9:15
  • 1
    previous links, and just noticed MariaDB in the error so linked its documentation too. Keep asking if you get stuck however.
    – danblack
    Commented Apr 22, 2020 at 9:54
  • 1
    The error is pointing to "getdate"; look that up. When you don't find it, search for "MySQL date functions".
    – Rick James
    Commented Apr 29, 2020 at 3:24

1 Answer 1

Reset to default
0

In fact, it is not easy to find the right command we need for queries in the MySQL documentation.
However, by testing, I saw that this should be fine:

sql = f"SELECT DATEDIFF(NOW(), timestamp), count_issue FROM total_unresolved_issue WHERE project = '{project}'"

DATEDIFF(NOW(), timestamp) returns the difference between two dates in days.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.