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. '%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 |
SELECT * FROM dt_tb
WHERE DATE_FORMAT(dt,'%b-%Y')='May-2004';
OR
SELECT * FROM dt_tb
WHERE DATE_FORMAT(dt,'%M-%Y')='December-2005';
Specifier | Description |
---|---|
%a | Abbreviated weekday name (Sun..Sat) |
%b | Abbreviated month name (Jan..Dec) |
%c | Month, numeric (0..12) |
%D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
%d | Day of the month, numeric (00..31) |
%e | Day of the month, numeric (0..31) |
%f | Microseconds (000000..999999) |
%H | Hour (00..23) |
%h | Hour (01..12) |
%I | Hour (01..12) |
%i | Minutes, numeric (00..59) |
%j | Day of year (001..366) |
%k | Hour (0..23) |
%l | Hour (1..12) |
%M | Month name (January..December) |
%m | Month, numeric (00..12) |
%p | AM or PM |
%r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S | Seconds (00..59) |
%s | Seconds (00..59) |
%T | Time, 24-hour (hh:mm:ss) |
%U | Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 |
%u | Week (00..53), where Monday is the first day of the week; WEEK() mode 1 |
%V | Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X |
%v | Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x |
%W | Weekday name (Sunday..Saturday) |
%w | Day of the week (0=Sunday..6=Saturday) |
%X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
%x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y | Year, numeric, four digits |
%y | Year, numeric (two digits) |
%% | A literal % character |
%x | x, for any “x” not listed above |
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 ReferencesMONTH()
DAY()
NOW() : Current Date with Time
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.... |