How to query a date using strftime to format

18
February 12, 2019, at 12:20 PM

I am trying to grab all the records for the month. The string I have to query with is in this format 2019-01-12. The record I am querying for is a DateTime record so it has a format like this 2018-08-11 13:39:22.959330.

So I am trying to structure a query that would achieve this

Score.where('user_id = ? AND date_closed.strftime("%Y-%m) = ?', current_user.id, date)

Where date is the 2019-01-12 string. The above code produces the error

FUNCTION date_closed.strftime does not exist

I did google but was unable to locate something that achieved this. Most solutions involved searching inside a range of dates, I would really like to try to keep the search method the same.

Answer 1

You have the DATE_FORMAT function for that https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

DATE_FORMAT(date_closed, "%Y-%m")

EDIT: you'll have to also format the date you are passing to the query

Answer 2

You can use mysql data functionos YEAR and MONTH:

Score.where('user_id = ? AND YEAR(date_closed) = ? AND MONTH(date_closed) = ?', current_user.id, date.year, date.month)
READ ALSO
Extract data from a database if a specific value in is followed by a another specific value

Extract data from a database if a specific value in is followed by a another specific value

I have a database in MySQL where one of the columns consists of different types of events and I want to extract specific data to pythonFor example:

38
SQL, Group by one column with one distinct column

SQL, Group by one column with one distinct column

I have a table with following structure

21
Get sum using DATE_FORMAT() and IN

Get sum using DATE_FORMAT() and IN

I've got a query that looks a little bit like this :

31
In select query include placeholder results where no direct result was found

In select query include placeholder results where no direct result was found

I have a table of race resultsNot all competitors enter every single race, but I would like to include a table which shows those races they didn't enter with blanks and a hyphen "-" value in the result column

32