

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Format date with DATE_FORMAT() and STR_TO_DATE() in MySQL
Let us first create a table −
mysql> create table DemoTable ( DueDate varchar(100) ); Query OK, 0 rows affected (0.62 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values('August 04,2019'); Query OK, 1 row affected (0.25 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+----------------+ | DueDate | +----------------+ | August 04,2019 | +----------------+ 1 row in set (0.00 sec)
Following is the query to format date −
mysql> set @stringToDate=(select date_format(str_to_date(DueDate, '%M %d,%Y'), '%Y-%m-%d') from DemoTable); Query OK, 0 rows affected (0.02 sec)
Let us use a select statement to display the date −
mysql> select @stringToDate;
This will produce the following output −
+---------------+ | @stringToDate | +---------------+ | 2019-08-04 | +---------------+ 1 row in set (0.00 sec)
- Related Questions & Answers
- Replace date format with MySQL STR_TO_DATE
- How to change date format with DATE_FORMAT() in MySQL?
- Set format specifier in MySQL STR_TO_DATE() and convert string to date
- How GET_FORMAT() function can combine with DATE_FORMAT() and STR_TO_DATE() function?
- What are different date format characters used by MySQL DATE_FORMAT() function?
- How to use together the date and time format characters in MySQL DATE_FORMAT() function?
- Convert Date not in a proper format with MySQL?
- Select date from MySQL and format to text?
- Format date to display month names with the entire date in MySQL?
- Format date in MySQL to return MonthName and Year?
- What MySQL returns if specified format string is not as per accordance with the date string passed as arguments to STR_TO_DATE() function?
- Convert MySQL Unix-Timestamp format to date format?
- What are the different time format characters used by MySQL DATE_FORMAT() function?
- MySQL ORDER BY Date field not in date format?
- Extract Numeric Date Value from Date Format in MySQL?
Advertisements