Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Get date format DD/MM/YYYY with MySQL Select Query?
Use the STR_TO_DATE() function from MySQL to set a date format for displaying DD/MM/YYYY date. The syntax is as follows −
SELECT STR_TO_DATE(yourColumnName,’%d/%m/%Y) as anyVariableName from yourTableName.
To understand the above syntax, let us create a table −
mysql> create table DateFormatDemo −> ( −> IssueDate varchar(100) −> ); Query OK, 0 rows affected (0.54 sec)
Inserting some string dates into the table. The query to insert date is as follows −
mysql> insert into DateFormatDemo values('26/11/2018');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DateFormatDemo values('27/11/2018');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DateFormatDemo values('2/12/2018');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DateFormatDemo values('3/12/2018');
Query OK, 1 row affected (0.14 sec)Now you can display all dates which I have inserted above. The query is as follows −
mysql> select *from DateFormatDemo;
The following is the output −
+------------+ | IssueDate | +------------+ | 26/11/2018 | | 27/11/2018 | | 2/12/2018 | | 3/12/2018 | +------------+ 4 rows in set (0.00 sec)
You can implement the syntax we discussed in the beginning to convert string to date format. The query is as follows −
mysql> select STR_TO_DATE(IssueDate, '%d/%m/%Y') StringToDateFormatExample from DateFormatDemo;
The following is the output −
+---------------------------+ | StringToDateFormatExample | +---------------------------+ | 2018-11-26 | | 2018-11-27 | | 2018-12-02 | | 2018-12-03 | +---------------------------+ 4 rows in set (0.00 sec)
Updated on: 29-Jun-2020
2K+ Views
Advertisements