php

sql

mysql

phrase

how do I get month from date in mysql

Viewed 72 times

I want to be able to fetch results from mysql with a statement like this:

SELECT * 
  FROM table 
 WHERE amount > 1000

But I want to fetch the result constrained to a certain a month and year (based on input from user)... I was trying like this:

SELECT * 
  FROM table 
 WHERE amount > 1000 
   AND dateStart = MONTH('$m')

...$m being a month but it gave error.

In that table, it actually have two dates: startDate and endDate but I am focusing on startDate. The input values would be month and year. How do I phrase the SQL statement that gets the results based on that month of that year?

Answers

You were close - got the comparison backwards (assuming startDate is a DATETIME or TIMESTAMP data type):

SELECT * 
  FROM table 
 WHERE amount > 1000 
   AND MONTH(dateStart) = {$m}

Caveats:

Mind that you are using mysql_escape_string or you risk SQL injection attacks.
Function calls on columns means that an index, if one exists, can not be used

Alternatives:

Because using functions on columns can't use indexes, a better approach would be to use BETWEEN and the STR_TO_DATE functions:

WHERE startdate BETWEEN STR_TO_DATE([start_date], [format]) 
                    AND STR_TO_DATE([end_date], [format])

See the documentation for formatting syntax.

Reference:

MONTH
YEAR
BETWEEN
STR_TO_DATE

Wednesday, December 7, 2022

mahendiran.b

Use new Date() to generate a new Date object containing the current date and time.

var today = new Date();
var dd = String(today.getDate()).padStart(2, '0');
var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0!
var yyyy = today.getFullYear();

today = mm + '/' + dd + '/' + yyyy;
document.write(today);

This will give you today's date in the format of mm/dd/yyyy.

Simply change today = mm +'/'+ dd +'/'+ yyyy; to whatever format you wish.

Saturday, September 24, 2022

raghuram_nadiminti

So you want an AND search using each of the words entered, rather than the exact string? Howabout something like this:

$searchTerms = explode(' ', $bucketsearch);
$searchTermBits = array();
foreach ($searchTerms as $term) {
    $term = trim($term);
    if (!empty($term)) {
        $searchTermBits[] = "bucketname LIKE '%$term%'";
    }
}

...

$result = mysql_query("SELECT * FROM buckets WHERE ".implode(' AND ', $searchTermBits).");

this will give you a query like:

SELECT * FROM buckets WHERE bucketname LIKE '%apple%' AND bucketname LIKE '%and%' AND bucketname LIKE '%pear%'

change the AND to an OR if you want to match any of the search terms rather than all. Further improvements could involve defining some stop words like 'and' to give better results.

Thursday, September 1, 2022

user6741253

Since (around) PHP 5.2, PHP has had a built in class/object for dealing with Dates and Times, called DateTime. In a void, it's always better to use a built-in than to wrangle with the messy details yourself.

The DateTime constructor (or the date_create function) accepts a date in any format understood by strToTime. All you need to know about strToTime is it's magic voodoo that will correctly recognize a date in almost any string format. When I first encountered strToTime I had the same internal reaction you're having now ("that's bullshit/seems unreliable"). It's not. It Just Works in a way that your own fragile understanding of dates never will (and if you think you understand dates, you don't. Trust Me.)

So, pull the information from MySQL as a Date/Time string, and immediately create a PHP date Object. Use the date_format method (with some handy constants) when/if you need the date again as a string.

Monday, November 21, 2022

jnes

DateTime.Now.ToString("M/d/yyyy");

http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

Monday, November 7, 2022

horejsek

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

sql