Select Row by Datepart()
I Need to Get Rows from the Database Where the Records Are of One Month. I Tried This Select: $Result = Mysql_Query("Select * from My_Table Where...
I need to get rows from the database where the records are of one month. I tried this SELECT:
$result = mysql_query("SELECT * FROM my_table WHERE DATEPART('month', date_column)=11");
In database is a lot of rows that have a date in the 11. month, but i do not get any results. Can anyone help me? Thank!
4 Answers
There is no DATEPART function in MySQL. Use MONTH(date_column) or EXTRACT(MONTH FROM date_column) instead.
SELECT * FROM my_table WHERE MONTH(date_column)=11
If you have an index on date_column and you concern about performance it is better to NOT apply functions over the column. (Be aware that this solution, do not anwsers exactly what you asked, becouse it also involves the year)
-- For mysql specific:
SELECT * FROM my_table
WHERE date_column >= '2012-11-01'
and date_column < '2012-11-01' + INTERVAL 1 MONTH
-- For tsql specific:
SELECT * FROM my_table
WHERE date_column >= '2012-11-01'
and date_column < DATEADD(month,1,'2012-11-01')
If your DATEPART is not working in MySQL then you can use:
SELECT *
FROM MyTable
WHERE MONTH(joiningDate) = MONTH(NOW())-1
AND YEAR(joiningDate) = YEAR(NOW());
It will return all records who have inserted into MyTable in the last month.
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
