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

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 −

Format Description
%a Abbreviated weekday name (Sun to Sat)
%b Abbreviated month name (Jan to Dec)
%c Numeric month name (0 to 12)
%D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d Day of the month as a numeric value (01 to 31)
%e Day of the month as a numeric value (0 to 31)
%f Microseconds (000000 to 999999)
%H Hour (00 to 23)
%h Hour (00 to 12)
%I Hour (00 to 12)
%i Minutes (00 to 59)
%j Day of the year (001 to 366)
%k Hour (0 to 23)
%l Hour (1 to 12)
%M Month name in full (January to December)
%m Month name as a numeric value (00 to 12)
%p AM or PM
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S Seconds (00 to 59)
%s Seconds (00 to 59)
%T Time in 24 hour format (hh:mm:ss)
%U Week where Sunday is the first day of the week (00 to 53)
%u Week where Monday is the first day of the week (00 to 53)
%V Week where Sunday is the first day of the week (01 to 53). Used with %X
%v Week where Monday is the first day of the week (01 to 53).
%W Weekday name in full (Sunday to Saturday)
%w Day of the week where Sunday = 0 and Saturday = 6
%X Year for the week where Sunday is the first day of the week.
%x Year for the week where Monday is the first day of the week
%Y Year as a numeric, 4-digit value
%y Year 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: 2020-06-25T11:25:58+05:30

753 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements