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
How to convert MM/YY to YYYY-MM-DD with a specific day in MySQL?
To convert, use STR_TO_DATE(), as in the below syntax. Concatenate the day value with CONCAT() −
select str_to_date(concat('yourDateValue/', yourColumnName), '%d/%m/%y') as anyAliasName
from yourTableName;Let us create a table −
mysql> create table demo46 −> ( −> id int not null auto_increment primary key, −> short_date varchar(20) −> ); Query OK, 0 rows affected (0.60 sec)
Insert some records into the table with the help of insert command −
mysql> insert into demo46(short_date) values('09/18');
Query OK, 1 row affected (0.08 sec)
mysql> insert into demo46(short_date) values('12/20');
Query OK, 1 row affected (0.12 sec)
mysql> insert into demo46(short_date) values('11/20');
Query OK, 1 row affected (0.20 sec)Display records from the table using select statement −
mysql> select *from demo46;
This will produce the following output −
+----+------------+ | id | short_date | +----+------------+ | 1 | 09/18 | | 2 | 12/20 | | 3 | 11/20 | +----+------------+ 3 rows in set (0.00 sec)
Following is the query to convert MM/YY to YYYY-MM-DD in MySQL.
mysql> select str_to_date(concat('24/', short_date), '%d/%m/%y') as Convert_In_Full_Date
−> from demo46;This will produce the following output −
+----------------------+ | Convert_In_Full_Date | +----------------------+ | 2018−09−24 | | 2020−12−24 | | 2020−11−24 | +----------------------+ 3 rows in set (0.00 sec)
Updated on: 19-Nov-2020
210 Views
Advertisements