SELECT row by DATEPART()

Question

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!

Please don't use the `mysql_*` functions as they are in the [deprecation process](http://news.php.net/php.internals/53799). Use [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) instead and [be a better PHP Developer](http://jason.pureconcepts.net/2012/08/better-php-developer/). — Jason McCreary, Nov 15 '12 at 16:34
with even minimal error handling, you'd have found out why this wasn't working: `$result = mysql_query(...) or die(mysql_error());`. never EVER assume a query succeeded. ALWAYS check for errors. — Marc B, Nov 15 '12 at 17:20

score 34 · Accepted Answer · answered Nov 15 '12 at 16:57

34

There is no DATEPART function in MySQL. Use MONTH(date_column) or EXTRACT(MONTH FROM date_column) instead.

answered Nov 15 '12 at 16:57

MvG

57,380
22
148
276

score 6 · Answer 2 · answered Nov 15 '12 at 16:34

6

    SELECT * FROM my_table WHERE MONTH(date_column)=11

answered Nov 15 '12 at 16:34

Imre L

6,159
24
32

Saic Siquot · Answer 3 · 2012-11-15T18:54:31.343

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

(mysql fiddle)

-- For tsql specific:
SELECT * FROM my_table
WHERE date_column >= '2012-11-01'
  and date_column < DATEADD(month,1,'2012-11-01')

(tsql fiddle)

score -1 · Answer 4 · edited Jul 20 '21 at 16:13

-1

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.

edited Jul 20 '21 at 16:13

marc_s

732,580
175
1,330
1,459

answered Dec 14 '17 at 08:40

Shubham Verma

8,783
6
58
79

SELECT row by DATEPART()

4 Answers4

Linked