How to select only month from date field?

Question

I have a database record of payments. In date field I have all 3 option dd-month-year from a date-picker.

datebase of payment

Now I want to know total payment submit of a month or any specific date for date.

   if($_POST['date']!='' ){
      $query ="SELECT SUM(submit) AS value_sum FROM payment where date='".$_POST['date']."' ";
      $result=mysql_query($query,$con);
      $row = mysql_fetch_assoc($result); 
      $sum = $row['value_sum'];
   }

How can I count total submit of a month from this table?

For month I am using:

<p class="name">
<td>
  <label for="name1">By Month</label>
</td>
<td>
<select name="month">
  <option>Month</option>
  <option value="JAN">January</option>
  <option value="FEB">February</option>
  <option value="MAR">March</option>
  <option value="APR">April</option>
  <option value="MAY">May</option>
  <option value="JUN">June</option>
  <option value="JUL">July</option>
  <option value="AUG">August</option>
  <option value="SEP">September</option>
  <option value="OCT">October</option>
  <option value="NOV">November</option>
  <option value="DEC">December</option>
</select>
</td>
</p>

what is the type of date column? is it a date type or varchar? — pataluc, Jun 17 '13 at 14:57
https://www.google.com/search?q=select+only+month+from+date+in+mysql+database&oq=select+only+month+from+date+in+mysql+database&aqs=chrome.0.57j62l3.1818j0&sourceid=chrome&ie=UTF-8 — Sebas, Jun 17 '13 at 14:57
Would saying use PDO or MySQLi offend you? Because if not... Use PDO or MySQLi — user2406160, Jun 17 '13 at 14:59
This question should help you: http://stackoverflow.com/questions/2039839/how-do-i-get-month-from-date-in-mysql. Look up MONTHNAME() : http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_monthname — Andrew Briggs, Jun 17 '13 at 15:02

nurdglaw · Answer 1 · 2013-06-17T17:28:00.947

It's probably best to extract the month from date using MySQL's MONTH() function. So, assuming that you can arrange for January to be passed as 1, February as 2, etc. use a query like

SELECT SUM(submit) AS value_SUM
FROM payment
WHERE MONTH(date)=required_month
AND YEAR(date)=required_year

Take a look at the MySQL date and time functions when you have a minute.

After checking that link, I see there's a MONTHNAME function which would work if you're getting passed January, February etc. In that case, the query is

SELECT SUM(submit) AS value_sum
FROM payment
WHERE MONTHNAME(date)=required_month
AND YEAR(date)=required_year

Editted to add AND YEAR... to both queries after comment from Barmar. Of course, if you do want the total for all January's, you can leave this out. Also, if you don't want to make your user specify the current year, or you want it to default, arrange that required_year is YEAR(CURRENT_DATE()) in your PHP code.

Editted again after the OPs comment below, and taking into account his comment above that date is varchar.

I assume that $_POST['date']contains a date in the month you want the totals from. Substitute the lines below for your line `$query = ...'

$query =  'SELECT SUM(submit) AS value_sum FROM payment';
$query .= 'WHERE MONTH(CONVERT(datetime, date))';
$query .= '=MONTH(CONVERT(datetime,' . $_POST['date'] . '))';
$query .= 'AND YEAR(CONVERT(datetime, date))';
$query .= '=YEAR(CONVERT(datetime, ' . $_POST['date'] . '))';

This will combine months in different years, which is probably not what is wanted. — Barmar, Jun 17 '13 at 15:10
This answer is quite logical after the edit: Selecting the specific month from a specific year makes sense after all. — Jeff Noel, Jun 17 '13 at 15:40

Sander · Accepted Answer · 2013-06-18T18:19:52.953

1

$query ="SELECT SUM(submit) AS value_sum FROM payment where date LIKE '%".$_POST['date']."%' ";

With something like this you get all results where, for example, date has 'MAY' in it.

edited Jun 18 '13 at 18:19

answered Jun 17 '13 at 14:59

Sander

392
2
13

score 0 · Answer 3 · answered Jun 17 '13 at 15:01

0

SELECT SUM(submit) AS value_sum FROM payment where date BETWEEN $_POST['date1'] AND     
$_POST['date2'];

answered Jun 17 '13 at 15:01

manuthalasseril

1,054
7
17
33

How to select only month from date field?

3 Answers3