How to insert date in single quotes with MySQL date formats?


To insert the date with date formats, use the str_to_date() function with date in single quotes. The following is the syntax −

insert into yourTableName values(Value1,value2,......ValueN,str_to_date(‘anyDate’,’%Y-%m-%d’));

Here are the Date Formats in MySQL −

FormatDescription
%aAbbreviated weekday name (Sun to Sat)
%bAbbreviated month name (Jan to Dec)
%cNumeric month name (0 to 12)
%DDay of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%dDay of the month as a numeric value (01 to 31)
%eDay of the month as a numeric value (0 to 31)
%fMicroseconds (000000 to 999999)
%HHour (00 to 23)
%hHour (00 to 12)
%IHour (00 to 12)
%iMinutes (00 to 59)
%jDay of the year (001 to 366)
%kHour (0 to 23)
%lHour (1 to 12)
%MMonth name in full (January to December)
%mMonth name as a numeric value (00 to 12)
%pAM or PM
%rTime in 12 hour AM or PM format (hh:mm:ss AM/PM)
%SSeconds (00 to 59)
%sSeconds (00 to 59)
%TTime in 24 hour format (hh:mm:ss)
%UWeek where Sunday is the first day of the week (00 to 53)
%uWeek where Monday is the first day of the week (00 to 53)
%VWeek where Sunday is the first day of the week (01 to 53). Used with %X
%vWeek where Monday is the first day of the week (01 to 53).
%WWeekday name in full (Sunday to Saturday)
%wDay of the week where Sunday = 0 and Saturday = 6
%XYear for the week where Sunday is the first day of the week.
%xYear for the week where Monday is the first day of the week
%YYear as a numeric, 4-digit value
%yYear as a numeric, 2-digit value

Let us first create a table −

mysql> create table BookDateDemo
   −> (
      −> BookName varchar(200),
      −> BookIssueDate datetime
   −> );
Query OK, 0 rows affected (0.44 sec)

Now implement the syntax we discussed above to insert date with MySQL date format. Here we have set the date in single quote −

mysql> insert into BookDateDemo values('Let us C',str_to_date('2018-11-28','%Y-%m-%d'));
Query OK, 1 row affected (0.11 sec)

mysql> insert into BookDateDemo values('The C++ Programming',str_to_date('2018-11-29','%Y-%m-%d'));
Query OK, 1 row affected (0.16 sec)

mysql> insert into BookDateDemo values('C in Depth',str_to_date('2018-11-20','%Y-%m-%d'));
Query OK, 1 row affected (0.23 sec)

Display all records with the help of select statement. The query to display all records are as follows −

mysql> select *from BookDateDemo;

The following is the output −

+---------------------+---------------------+
| BookName            | BookIssueDate       |
+---------------------+---------------------+
| Let us C            | 2018-11-28 00:00:00 |
| The C++ Programming | 2018-11-29 00:00:00 |
| C in Depth          | 2018-11-20 00:00:00 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

Updated on: 25-Jun-2020

550 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements