Menu
My SQL table looks like this:
id (int) | date (date) | text1 (varchar) | text2 (varchar)
I want to select the lines whose date suits a given month and year, regardless of the day.
Both month and year are given in the select-statement as integers.
So the missing thing is the where-clause. Perhaps extract() is the thing I'm looking for, but I don't know how to use it with the two integers, e.g. 2011 and 02.
540
To get the year and the month columns, use the EXTRACT(part FROM date) function. In this solution, the part argument is replaced by YEAR and MONTH to get the year and the month separately, each in its own column. You can learn more about EXTRACT() in the official MySQL documentation.
To select all entries from a particular month in MySQL, use the monthname() or month() function.
We can retrieve the current month value in SQL using the MONTH() and DATEPART() functions along with the GETDATE() function. To retrieve the name of the month functions in SQL such as DATENAME() and FORMAT() are used.
Use SQL Server's YEAR() function if you want to get the year part from a date. This function takes only one argument – a date, in one of the date and time or date data types. (In our example, the column BirthDate is a date data type). The argument can be a column name or an expression.
You can use extract:
SELECT * FROM yourtable
WHERE EXTRACT(month FROM "date") = 2
AND EXTRACT(year FROM "date") = 2011
But in this case you could also do this:
SELECT * FROM yourtable
WHERE "date" >= '2011-02-01' AND "date" < '2011-03-01'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
