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
MySQL extract year from date format?
To extract year from date format, you can use in-built function YEAR() from MySQL. The query is as follows −
mysql> SELECT YEAR(curdate()) as OnlyYearFromCurrentDate;
The following is the output −
+-------------------------+ | OnlyYearFromCurrentDate | +-------------------------+ | 2018 | +-------------------------+ 1 row in set (0.00 sec)
Or you can do in the following way −
mysql> SELECT YEAR(date(now())) as OnlyYearFromCurrentDate;
The following is the output −
+-------------------------+ | OnlyYearFromCurrentDate | +-------------------------+ | 2018 | +-------------------------+ 1 row in set (0.00 sec)
With that, you can even achieve this when column is varchar. Let us first create a table −
mysql> create table ExtractYearDemo −> ( −> YourDueTime varchar(200) −> ); Query OK, 0 rows affected (1.15 sec)
Inserting records into the table with the help of insert command. The query is as follows −
mysql> insert into ExtractYearDemo values('27-10-2011');
Query OK, 1 row affected (0.16 sec)
mysql> insert into ExtractYearDemo values('28-11-2012');
Query OK, 1 row affected (0.15 sec)
mysql> insert into ExtractYearDemo values('29-12-2018');
Query OK, 1 row affected (0.14 sec)
mysql> insert into ExtractYearDemo values('01-01-2019');
Query OK, 1 row affected (0.15 sec)Displaying all records from the table with the help of SELECT statement. The query is as follows −
mysql> select *from ExtractYearDemo;
The following is the output −
+-------------+ | YourDueTime | +-------------+ | 27-10-2011 | | 28-11-2012 | | 29-12-2018 | | 01-01-2019 | +-------------+ 4 rows in set (0.00 sec)
The following is the query to extract year from table column when column is varchar type −
mysql> select YEAR(STR_TO_DATE(yourDueTime, "%d-%m-%Y")) As OnlyYearFromDate from ExtractYearDemo;
The following is the output displaying year −
+------------------+ | OnlyYearFromDate | +------------------+ | 2011 | | 2012 | | 2018 | | 2019 | +------------------+ 4 rows in set (0.00 sec)
Updated on: 30-Jul-2019
264 Views
Advertisements