How to format date in Query Statement Mysql?

0
votes

I'm using php with Mysql and I need to run a specific query when user selects month and year in a date_field. This query returns all records created by the month/year selected by user and I don't want relate days in a clause where inside the select. Since I need only records from month/year select, is there any ways how can I define inside the select only month/year?

Here is my Mysql statement with variable selected by user There are two tables related with inner join so I can 'grab' records with Date:

$quer_mesano = "Select b.name AS Canal                                  
                                from canal_canalvenda b
                                inner join meta_meta c On  c.canal_canalvenda_id_c = b.id
                                And c.deleted = 0
                                where c.periodo = '$month_year' //Here is the date value I just need to put in month/year
                                And b.deleted = 0
                                Group by Canal order by 1";

Can any one help me?

3
phpmysql
In your schema, how is column c.periodo defined? what format is $month_year in, can you give an example? - RobP
dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html, date_format() in particular. - Marc B

3 Answers

3
votes

This is very easy. Use DATE_FORMAT. Just change the Sql statement like this:

$quer_mesano = "Select b.name AS Canal                                  
                            from canal_canalvenda b
                            inner join meta_meta c On  c.canal_canalvenda_id_c = b.id
                            And c.deleted = 0
                            where DATE_FORMAT( c.periodo, '%Y-%m' ) = '$month_year' //Just put DATE_FORMAT
                            And b.deleted = 0
                            Group by Canal order by 1";
2
votes

DATE_FORMAT() you can use as shown below

$quer_mesano = "Select b.name AS Canal                                  
                            from canal_canalvenda b
                            inner join meta_meta c On  c.canal_canalvenda_id_c = b.id
                            And c.deleted = 0
                            where DATE_FORMAT( c.periodo, '%Y/%m' ) = '$month_year'
                            And b.deleted = 0
                            Group by Canal order by 1";

For month you have different types - %M Month name %m Month, numeric (00-12) and for year - %Y Year, four digits %y Year, two digits

1
votes

I convert the month year to YYYY-MM formate where it can be compared with the db. 

$quer_mesano = "Select b.name AS Canal                                  
                from canal_canalvenda b
                inner join meta_meta c On  c.canal_canalvenda_id_c = b.id
                And c.deleted = 0
                where DATE_FORMAT( c.periodo, '%Y-%m' ) = '" . date("Y-m", strtotime($month_year)) . "' //Here is the date value I just need to put in month/year
                And b.deleted = 0
                Group by Canal order by 1";