CODEWITHSUNDEEP
phpmysql
Anu
Anu

Reputation: 1129

Select rows based on month by using a date column in MySql

I have a query to extract some data based on date as follows

$duplicateCheckQuery = "SELECT * FROM vtag.supervisorupdate WHERE dateAdded=?";
$stmtDupQuery = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmtDupQuery, $duplicateCheckQuery)){
    echo "Details check in supervisor table SQL statement failed";
} else {
    mysqli_stmt_bind_param($stmtDupQuery,"s", $dateToCheck);
    mysqli_stmt_execute($stmtDupQuery);
    $result = mysqli_stmt_get_result($stmtDupQuery);
    $totalOut = mysqli_fetch_array($result);          
}

This query is based on date.

But now I want to extract data based on month. But I don't have such a month column to extract data. The only available date information is this dateAdded column. I know there is a MONTH function in MySql. But I am not sure how to use it to filter data from this dateAdded column. Anyone know how to do it?

Edit 1 Sample of date in dateAdded column as below

enter image description here

Upvotes: 0

Views: 830

Answers (2)

Araz Jafaripur
Araz Jafaripur

Reputation: 906

Try MONTH function:

SELECT * FROM vtag.supervisorupdate WHERE MONTH(dateAdded) = 1

1 = January.

If your field is not date value, cast it to date:

 CAST(dateAdded AS DATE) as newDate;

OR STR_TO_DATE(dateAdded, '%Y-%d-%m') as newDate

In the last:

SELECT * FROM vtag.supervisorupdate WHERE MONTH(STR_TO_DATE(dateAdded, '%Y-%d-%m')) = 1

Upvotes: 3

Hisham Elsayad
Hisham Elsayad

Reputation: 476

Your query should be

"SELECT * FROM vtag.supervisorupdate WHERE month(dateAdded) = ?"

Upvotes: 2

Related Questions