{"id":1752,"date":"2009-02-20T02:52:04","date_gmt":"2009-02-20T06:52:04","guid":{"rendered":"http:\/\/teamtutorials.com\/?p=1752"},"modified":"2013-12-07T19:33:59","modified_gmt":"2013-12-08T00:33:59","slug":"convert-a-mysql-date-field-using-php-functions","status":"publish","type":"post","link":"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions","title":{"rendered":"Convert a MySQL date field using PHP Functions"},"content":{"rendered":"
\n

You should not use this code on a production website.<\/h4>\n

Warning:<\/strong> This tutorial uses old techniques. It is insecure and will leave your server vulnerable SQL Injection attacks<\/strong>.This tutorials also uses mysql_ functions that are no longer support. For updated tutorials look for a PDO<\/a> or MySQLi tutorial.This post will be delete or revised in the future.<\/p>\n<\/div>\n

Today I am going to show you how to convert data returned from a MySQL database that was store as a date data type into a user friendly format. To do this you can take two different approaches. One way to do it is to use the function date_format() function in your query. The other way to to modify the date after it is returned.<\/p>\n

Since you are trying to convert the date I am assuming you can connect to a database, runa query, and display the results. If not check out some of our older tutorials:<\/p>\n

How to Access a MySQL Database Using PHP<\/a><\/h2>\n

Creating a Form that will Search a MySQL Database<\/a><\/h2>\n

You can use the function in the query to convert the date or after the results are returned.<\/p>\n

Using date_format() <\/p>\n

Here is an example of how to call the function in a query:<\/p>\n

\r\n$query = mysql_query(\"select * date_format(date, '%b %d') as newdate from `table`\")\r\n<\/pre>\n
Then when your results are returned simple echo $row[newdate]. The date I used will output the date in this format: Feb 18th (abbreviated month and numeric day with suffix).
\nYou need to pass the date string and the format mask to the function: date_format($date, $format)<\/p>\n
You can pretty much do this the same way after your results are returned.
\nFor example:<\/p>\n
\r\n\t$query = mysql_query(\"select * from `table` \");\t\r\n\twhile ($row = mysql_fetch_array($query)){\r\n\t\t$newdate = date_format(strtotime($row[date]), '%b %d');\r\n\t}\r\n<\/pre>\n
strtotime will convert a string date to a time stamp.<\/p>\n
Other formatting option for date_format()<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Specifier<\/th>\nDescription<\/th>\n<\/tr>\n
%a<\/td>\nAbbreviated weekday name (Sun..Sat)<\/td>\n<\/tr>\n
%b<\/td>\nAbbreviated month name (Jan..Dec)<\/td>\n<\/tr>\n
%c<\/td>\nMonth, numeric (0..12)<\/td>\n<\/tr>\n
%D<\/td>\nDay of the month with English suffix (0th, 1st, 2nd, 3rd, \u2026)<\/td>\n<\/tr>\n
%d<\/td>\nDay of the month, numeric (00..31)<\/td>\n<\/tr>\n
%e<\/td>\nDay of the month, numeric (0..31)<\/td>\n<\/tr>\n
%f<\/td>\nMicroseconds (000000..999999)<\/td>\n<\/tr>\n
%H<\/td>\nHour (00..23)<\/td>\n<\/tr>\n
%h<\/td>\nHour (01..12)<\/td>\n<\/tr>\n
%I<\/td>\nHour (01..12)<\/td>\n<\/tr>\n
%i<\/td>\nMinutes, numeric (00..59)<\/td>\n<\/tr>\n
%j<\/td>\nDay of year (001..366)<\/td>\n<\/tr>\n
%k<\/td>\nHour (0..23)<\/td>\n<\/tr>\n
%l<\/td>\nHour (1..12)<\/td>\n<\/tr>\n
%M<\/td>\nMonth name (January..December)<\/td>\n<\/tr>\n
%m<\/td>\nMonth, numeric (00..12)<\/td>\n<\/tr>\n
%p<\/td>\nAM or PM<\/td>\n<\/tr>\n
%r<\/td>\nTime, 12-hour (hh:mm:ss followed by AM or PM)<\/td>\n<\/tr>\n
%S<\/td>\nSeconds (00..59)<\/td>\n<\/tr>\n<\/table>\n
edit:
\nThis can also be done using the date function. Something like:<\/p>\n
\t\t$day = date(“d”,strtotime($row[‘date’]));
\n\t\t$month = date(“M”,strtotime($row[‘date’]));<\/p>\n
would give you Mar 03<\/p>\n","protected":false},"excerpt":{"rendered":"
Today I am going to show you how to convert data returned from a MySQL database that was store as a date data type into a user friendly format. To do this you can take two different approaches. One way to do it is to use the function date_format() function in your query. The other way to to modify the date after it is returned.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[73,24,17],"tags":[],"yoast_head":"\nConvert a MySQL date field using PHP Functions<\/title>\n<meta name=\"description\" content=\"Today I am going to show you how to convert data returned from a MySQL database that was store as a date data type into a user friendly format. To do this you can take two different approaches. One way to do it is to use the function date_format() function in your query. The other way to to modify the date after it is returned.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Johnathan Ward\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/teamtutorials.com\/#website\",\"url\":\"https:\/\/teamtutorials.com\/\",\"name\":\"Team Tutorials\",\"description\":\"Learn something new today\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/teamtutorials.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions#webpage\",\"url\":\"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions\",\"name\":\"Convert a MySQL date field using PHP Functions\",\"isPartOf\":{\"@id\":\"https:\/\/teamtutorials.com\/#website\"},\"datePublished\":\"2009-02-20T06:52:04+00:00\",\"dateModified\":\"2013-12-08T00:33:59+00:00\",\"author\":{\"@id\":\"https:\/\/teamtutorials.com\/#\/schema\/person\/3abea1fc71644afe035403357450b9d9\"},\"description\":\"Today I am going to show you how to convert data returned from a MySQL database that was store as a date data type into a user friendly format. To do this you can take two different approaches. One way to do it is to use the function date_format() function in your query. The other way to to modify the date after it is returned.\",\"breadcrumb\":{\"@id\":\"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/teamtutorials.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Convert a MySQL date field using PHP Functions\"}]},{\"@type\":\"Person\",\"@id\":\"https:\/\/teamtutorials.com\/#\/schema\/person\/3abea1fc71644afe035403357450b9d9\",\"name\":\"Johnathan Ward\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/teamtutorials.com\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/93d667fe5dc6df5c722e0df5eac14d40?s=96&d=mm&r=r\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/93d667fe5dc6df5c722e0df5eac14d40?s=96&d=mm&r=r\",\"caption\":\"Johnathan Ward\"},\"description\":\"Johnathan Ward is an experienced developer and consultant that writes tutorials to help other developers. In his day job, he is an IBM Watson Consultant with several years of experience deploying and customizing Watson Explorer solutions.\",\"sameAs\":[\"http:\/\/johnathanward.com\",\"https:\/\/twitter.com\/spyderman4g63\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Convert a MySQL date field using PHP Functions","description":"Today I am going to show you how to convert data returned from a MySQL database that was store as a date data type into a user friendly format. To do this you can take two different approaches. One way to do it is to use the function date_format() function in your query. The other way to to modify the date after it is returned.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions","twitter_misc":{"Written by":"Johnathan Ward","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebSite","@id":"https:\/\/teamtutorials.com\/#website","url":"https:\/\/teamtutorials.com\/","name":"Team Tutorials","description":"Learn something new today","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/teamtutorials.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions#webpage","url":"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions","name":"Convert a MySQL date field using PHP Functions","isPartOf":{"@id":"https:\/\/teamtutorials.com\/#website"},"datePublished":"2009-02-20T06:52:04+00:00","dateModified":"2013-12-08T00:33:59+00:00","author":{"@id":"https:\/\/teamtutorials.com\/#\/schema\/person\/3abea1fc71644afe035403357450b9d9"},"description":"Today I am going to show you how to convert data returned from a MySQL database that was store as a date data type into a user friendly format. To do this you can take two different approaches. One way to do it is to use the function date_format() function in your query. The other way to to modify the date after it is returned.","breadcrumb":{"@id":"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/teamtutorials.com\/web-development-tutorials\/convert-a-mysql-date-field-using-php-functions#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/teamtutorials.com\/"},{"@type":"ListItem","position":2,"name":"Convert a MySQL date field using PHP Functions"}]},{"@type":"Person","@id":"https:\/\/teamtutorials.com\/#\/schema\/person\/3abea1fc71644afe035403357450b9d9","name":"Johnathan Ward","image":{"@type":"ImageObject","@id":"https:\/\/teamtutorials.com\/#personlogo","inLanguage":"en-US","url":"https:\/\/secure.gravatar.com\/avatar\/93d667fe5dc6df5c722e0df5eac14d40?s=96&d=mm&r=r","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/93d667fe5dc6df5c722e0df5eac14d40?s=96&d=mm&r=r","caption":"Johnathan Ward"},"description":"Johnathan Ward is an experienced developer and consultant that writes tutorials to help other developers. In his day job, he is an IBM Watson Consultant with several years of experience deploying and customizing Watson Explorer solutions.","sameAs":["http:\/\/johnathanward.com","https:\/\/twitter.com\/spyderman4g63"]}]}},"_links":{"self":[{"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/posts\/1752"}],"collection":[{"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/comments?post=1752"}],"version-history":[{"count":0,"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/posts\/1752\/revisions"}],"wp:attachment":[{"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/media?parent=1752"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/categories?post=1752"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/tags?post=1752"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}