14,983
edits
PHP and MySQL syntax: Difference between revisions
Jump to navigation
Jump to search
→date format
| Line 165: | Line 165: | ||
=== date format === | === date format === | ||
{{kbd | key = <nowiki>YYYY-MM-DD</nowiki>}} | {{kbd | key = <nowiki>YYYY-MM-DD</nowiki>}} e.g. {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY}} was converted from {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY}} 01:23:45. | ||
* Using [http://www.w3schools.com/sql/func_date.asp MySQL DATE() Function] e.g. {{kbd | key = <nowiki>SELECT DATE( datetime_column );</nowiki>}} | * Using [http://www.w3schools.com/sql/func_date.asp MySQL DATE() Function] e.g. {{kbd | key = <nowiki>SELECT DATE( datetime_column );</nowiki>}} | ||
* Using [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_FORMAT( datetime_column, '%Y-%m-%d');</nowiki>}} | * Using [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_FORMAT( datetime_column, '%Y-%m-%d');</nowiki>}} | ||
{{kbd | key = <nowiki>YYYY-MM-01</nowiki>}}: First day of the specific month<ref>[http://stackoverflow.com/questions/11808232/how-do-i-get-the-first-day-of-the-current-month mysql - How do I get the first day of the current month? - Stack Overflow]</ref>. | {{kbd | key = <nowiki>YYYY-MM-01</nowiki>}}: First day of the specific month<ref>[http://stackoverflow.com/questions/11808232/how-do-i-get-the-first-day-of-the-current-month mysql - How do I get the first day of the current month? - Stack Overflow]</ref> e.g. {{CURRENTYEAR}}-{{CURRENTMONTH}}-01 was converted from {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY}} 01:23:45. | ||
* Using [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_FORMAT( datetime_column, '%Y-%m-01');</nowiki>}} | * Using [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_FORMAT( datetime_column, '%Y-%m-01');</nowiki>}} | ||
* Using [http://www.w3schools.com/sql/func_date_sub.asp MySQL DATE_SUB() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_SUB(DATE( datetime_column ), INTERVAL DAYOFMONTH(DATE( datetime_column ))-1 DAY);</nowiki>}} | * Using [http://www.w3schools.com/sql/func_date_sub.asp MySQL DATE_SUB() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_SUB(DATE( datetime_column ), INTERVAL DAYOFMONTH(DATE( datetime_column ))-1 DAY);</nowiki>}} | ||
* Using [http://www.w3resource.com/mysql/string-functions/mysql-concat-function.php MySQL CONCAT() function] & [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT CONCAT( DATE_FORMAT( datetime_column, '%Y-%m' ) , '-01' ) ;</nowiki>}} | * Using [http://www.w3resource.com/mysql/string-functions/mysql-concat-function.php MySQL CONCAT() function] & [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT CONCAT( DATE_FORMAT( datetime_column, '%Y-%m' ) , '-01' ) ;</nowiki>}} | ||
{{kbd | key = <nowiki>YYYYMM</nowiki>}} | {{kbd | key = <nowiki>YYYYMM</nowiki>}} e.g. {{CURRENTYEAR}}{{CURRENTMONTH}} was converted from {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY}} 01:23:45. | ||
* Using [https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract MySQL EXTRACT() function] e.g. {{kbd | key = <nowiki>SELECT EXTRACT(YEAR_MONTH FROM datetime_column);</nowiki>}} | * Using [https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract MySQL EXTRACT() function] e.g. {{kbd | key = <nowiki>SELECT EXTRACT(YEAR_MONTH FROM datetime_column);</nowiki>}} | ||
* Using [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_FORMAT( datetime_column, '%Y%m');</nowiki>}} | * Using [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_FORMAT( datetime_column, '%Y%m');</nowiki>}} | ||