Tech Chorus

When choosing the MySQL field type to store dates, date is the preferred type. MySQL stores dates in the format yyyy-mm-dd which is seldom used to print dates on web pages. As a PHP developer you will eventually come across a need to convert the MySQL date format into dd-mm-yyyy or mm-dd-yyyy or another format. This post tells you how you can do that in your PHP programs.

Let us create a hypothetical situation to demonstrate the program.

Create the MySQL table.

CREATE TABLE dates (
  name varchar(30),
  birthday date 
) 

Insert sample data with dates.

INSERT INTO dates (
name ,
birthday
)
VALUES (
'Natasha', '1960-05-10'
), (
'Joshua', '1943-04-03'
), (
'Alex', '1971-02-09'
), (
'Laura', '1932-11-13'
), (
'Tina', '1991-08-12'
);

Let us write a PHP program to connect to the MySQL database, select Laura's birthday and print it in various formats.

At this point in our code, Laura's birthay is stored in the variable $luara_birthday. We will perform date format conversion operations on this variable.

Birthday as is.

Create a PHP DateTime object.

PHP 5 onwards provides the date_format() function which is very useful to convert date formats. PHP's date() function accepts various characters to output date string. The date formats accepted date() can also be used with date_format().

The date() function understands the string "d-m-Y". Therefore date_format() also understands "d-m-Y".

Similarly we use the string "m-d-Y" to convert the date format to mm-dd-yyyy.

I often use these functions to convert date formats.