Formatting MySQL date value

SELECT date_format( '2023-05-12','%d/%m/%Y'); # Output 12/05/2023
SELECT date_format( '2023-06-23','%d-%M-%Y'); # 23-June-2023
SELECT date_format( '2023-05-12','%m/%d/%Y %T'); # 05/12/2023 00:00:00
SELECT date_format( '2023-07-23','%W %D %M %Y %T'); #Sunday 23rd July 2023 00:00:00
SELECT date_format( '2023-07-23 22:54:58','%W %D %M %Y %T');#Sunday 23rd July 2023 22:54:58
We can directly get date format from MySQL table by using date_format function of MySQL. Here we will not use any php side function to get date format. Here is the basic syntax to get the date format from MySQL table.
SELECT date_format( dt, '%m/%d/%Y %T' ) as my_date FROM dt_tb
Here dt_tb is the table name and dt is the field name. You can see the formatting is done like this 'm/%d/%Y %T' . This formatting we can change to display date in different formats . Here are some of the formats and different possible values.
'%W %D %M %Y %T'Wednesday 5th May 2004 23:56:25
'%a %b %e %Y %H:%i'Wed May 5 2004 23:56
'%m/%d/%Y %T'05/05/2004 23:56:25
'%d/%m/%Y'05/05/2004
'%m-%d-%y'04-08-13


You can read about data formatting in mysql query here

Here is the sql code to create and fill the table with records

CREATE TABLE dt_tb (
id int(2) NOT NULL auto_increment,
dt datetime NOT NULL default '0000-00-00 00:00:00',
dt2 date NOT NULL default '0000-00-00',
PRIMARY KEY (id)
) TYPE=MyISAM;


# # Dumping data for table `dt_tb` #

INSERT INTO dt_tb VALUES (1, '2004-10-26 00:00:00', '2005-01-25'); INSERT INTO dt_tb VALUES (2, '2004-05-05 23:56:25', '2005-06-12'); INSERT INTO dt_tb VALUES (3, '2005-12-08 13:20:10', '2005-06-06');
SQL Date References MONTH() DAY() NOW() : Current Date with Time
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    gurpreet grewal

    29-03-2010

    this is very useful website ....... to read it i solve my prob.... thanx to plus to net.
    kasun

    16-09-2010

    hi, i want to know about how to read blob file in mysql database..but i know how to insert photo in to the data base. plz help me....

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2023 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer