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

SELECT date_format( dt, '%m/%d/%Y %T' ) as my_date FROM dt_tb

Sample query using date format

SELECT * FROM dt_tb 
	WHERE DATE_FORMAT(dt,'%b-%Y')='May-2004';

SELECT * FROM dt_tb 
	WHERE DATE_FORMAT(dt,'%M-%Y')='December-2005';

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');

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

← Subscribe to our YouTube Channel here