- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
MySQL - YEAR() Function
The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.
The MYSQL YEAR() function is used to retrieve and return the year of the given date or, date time expression. This function returns a numerical value ranging from 1000 to 9999.
Syntax
Following is the syntax of the above function –
YEAR(date);
Where, date is the date value from which you need to retrieve the year.
Example 1
Following example demonstrates the usage of the YEAR() function –
mysql> SELECT YEAR('2019-05-25');
+--------------------+
| YEAR('2019-05-25') |
+--------------------+
| 2019 |
+--------------------+
1 row in set (0.01 sec)
Example 2
Following is another example of this function –
mysql> SELECT YEAR('2008-09-17');
+--------------------+
| YEAR('2008-09-17') |
+--------------------+
| 2008 |
+--------------------+
1 row in set (0.00 sec)
Example 3
If the YEAR value in the given date is 0 this function returns 0 —
mysql> SELECT YEAR('0000-08-20');
+--------------------+
| YEAR('0000-08-20') |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT YEAR('0000-00-07');
+--------------------+
| YEAR('0000-00-07') |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
Example 4
If you pass an empty string or a non-string value as an argument this function returns NULL.
mysql> SELECT YEAR('');
+----------+
| YEAR('') |
+----------+
| NULL |
+----------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT YEAR(1990-11-11);
+------------------+
| YEAR(1990-11-11) |
+------------------+
| NULL |
+------------------+
1 row in set, 1 warning (0.00 sec)
Example 5
We can also pass the date-time expression as an argument to this function –
mysql> SELECT YEAR('2015-09-05 09:40:45.2300');
+----------------------------------+
| YEAR('2015-09-05 09:40:45.2300') |
+----------------------------------+
| 2015 |
+----------------------------------+
1 row in set (0.00 sec)
Example 6
In the following example we are retrieving the year from the current date —
mysql> SELECT YEAR(CURDATE()); +-----------------+ | YEAR(CURDATE()) | +-----------------+ | 2021 | +-----------------+ 1 row in set (0.00 sec)
Example 7
In the following example we are retrieving the year from the current timestamp —
mysql> SELECT YEAR(CURRENT_TIMESTAMP()); +---------------------------+ | YEAR(CURRENT_TIMESTAMP()) | +---------------------------+ | 2021 | +---------------------------+ 1 row in set (0.00 sec)
Example 8
You can also pass the column name as an argument to this function. Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –
mysql> CREATE TABLE MyPlayers(
ID INT,
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Date_Of_Birth date,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255),
PRIMARY KEY (ID)
);
Now, we will insert 7 records in MyPlayers table using INSERT statements:
mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');
Following query retrieves and prints the year from all the entities of the Date_Of_Birth column of the table MyPlayers —
mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country, YEAR(Date_Of_Birth) FROM MyPlayers; +------------+------------+---------------+-------------+---------------------+ | First_Name | Last_Name | Date_Of_Birth | Country | YEAR(Date_Of_Birth) | +------------+------------+---------------+-------------+---------------------+ | Shikhar | Dhawan | 1981-12-05 | India | 1981 | | Jonathan | Trott | 1981-04-22 | SouthAfrica | 1981 | | Kumara | Sangakkara | 1977-10-27 | Srilanka | 1977 | | Virat | Kohli | 1988-11-05 | India | 1988 | | Rohit | Sharma | 1987-04-30 | India | 1987 | | Ravindra | Jadeja | 1988-12-06 | India | 1988 | | James | Anderson | 1982-06-30 | England | 1982 | +------------+------------+---------------+-------------+---------------------+ 7 rows in set (0.17 sec)
Example 9
Suppose we have created a table named dispatches_data with 5 records in it using the following queries –
mysql> CREATE TABLE dispatches_data(
ProductName VARCHAR(255),
CustomerName VARCHAR(255),
DispatchTimeStamp timestamp,
Price INT,
Location VARCHAR(255)
);
insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad');
insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam');
insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada');
insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai');
insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');
Following query retrieves the year values from the DispatchTimeStamp column —
mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, YEAR(DispatchTimeStamp) FROM dispatches_data; +-------------+--------------+---------------------+-------+-------------------------+ | ProductName | CustomerName | DispatchTimeStamp | Price | YEAR(DispatchTimeStamp) | +-------------+--------------+---------------------+-------+-------------------------+ | Key-Board | Raja | 2019-05-04 15:02:45 | 7000 | 2019 | | Earphones | Roja | 2019-06-26 14:13:12 | 2000 | 2019 | | Mouse | Puja | 2019-12-07 07:50:37 | 3000 | 2019 | | Mobile | Vanaja | 2018-03-21 16:00:45 | 9000 | 2018 | | Headset | Jalaja | 2018-12-30 10:49:27 | 6000 | 2018 | +-------------+--------------+---------------------+-------+-------------------------+ 5 rows in set (0.12 sec)
Example 10
Suppose we have created a table named SubscriberDetails with 5 records in it using the following queries –
mysql> CREATE TABLE SubscriberDetails (
SubscriberName VARCHAR(255),
PackageName VARCHAR(255),
SubscriptionTimeStamp timestamp
);
insert into SubscriberDetails values('Raja', 'Premium', TimeStamp('2020-10-21 20:53:49'));
insert into SubscriberDetails values('Roja', 'Basic', TimeStamp('2020-11-26 10:13:19'));
insert into SubscriberDetails values('Puja', 'Moderate', TimeStamp('2021-03-07 05:43:20'));
insert into SubscriberDetails values('Vanaja', 'Basic', TimeStamp('2021-02-21 16:36:39'));
insert into SubscriberDetails values('Jalaja', 'Premium', TimeStamp('2021-01-30 12:45:45'));
Following query retrieves and displays the subscription year for all the records —
mysql> SELECT SubscriberName, PackageName, SubscriptionTimeStamp, YEAR(SubscriptionTimeStamp) FROM SubscriberDetails; +----------------+-------------+-----------------------+-----------------------------+ | SubscriberName | PackageName | SubscriptionTimeStamp | YEAR(SubscriptionTimeStamp) | +----------------+-------------+-----------------------+-----------------------------+ | Ram | Premium | 2020-10-21 20:53:49 | 2020 | | Rahman | Basic | 2020-11-26 10:13:19 | 2020 | | Robert | Moderate | 2021-03-07 05:43:20 | 2021 | | Radha | Basic | 2021-02-21 16:36:39 | 2021 | | Rajiya | Premium | 2021-01-30 12:45:45 | 2021 | +----------------+-------------+-----------------------+-----------------------------+ 5 rows in set (0.10 sec)
