MySQL numeric functions

• ABS
• SQRT
• MOD
• CEIL and CEILING
• FLOOR
• RAND
• ROUND
• SIGN
• POW and POWER
• SIN
• ASIN
• COS
• ACOS
• TAN
• ATAN
• COT

MySQL string functions

• LENGTH
• CONCAT
• INSERT
• LOWER
• UPPER
• LEFT
• RIGHT
• TRIM
• REPLACE
• SUBSTRING
• REVERSE

MySQL Date/Time functions

• CURDATE and CURRENT_DATE
• CURTIME and CURRENT_TIME
• NOW and SYSDATE
• UNIX_TIMESTAMP
• FROM_UNIXTIME
• MONTH
• MONTHNAME
• DAYNAME
• DAYOFWEEK
• WEEK
• DAYOFYEAR
• DAYOFMONTH
• YEAR
• TIME_TO_SEC
• SEC_TO_TIME
• DATE_ADD and ADDDATE
• DATE_SUB and SUBDATE
• ADDTIME
• SUBTIME
• DATEDIFF
• DATE_FORMAT
• WEEKDAY

MySQL aggregate functions

• MAX
• MIN
• COUNT
• SUM
• AVG

MySQL flow control functions

• IF
• IFNULL
• CASE

MySQL YEAR Function: Get The Year

The YEAR() function in MySQL is used to return the year of a specified date.

Here's a step-by-step guide on how to use the YEAR() function:

Step 1: Connect to MySQL.

Connect to your MySQL server using the MySQL command-line client or any other MySQL interface you prefer. Here is a basic command to connect to MySQL from the command line:

mysql -u root -p

Step 2: Select the database.

Once you're logged in, select the database where you want to use the YEAR() function:

USE mydatabase;

Replace mydatabase with the name of your database.

Step 3: Use the YEAR() function.

The basic syntax of the YEAR() function is as follows:

YEAR(date)

Here, date is the date from which to extract the year.

For example, to get the year of a specific date:

SELECT YEAR('2023-05-14');

This will return the year of the date '2023-05-14'. The result will be an integer representing the year, in this case, 2023.

Step 4: Exit MySQL.

When you're done, you can exit the MySQL interface by typing exit at the MySQL prompt and then pressing Enter.

That's it! You now know how to use the YEAR() function in MySQL. This function is particularly useful when you need to group, filter, or sort data by year.

1. How to use YEAR function in MySQL:

   • Description: The YEAR function in MySQL is used to extract the year from a date or datetime expression.
   • Syntax:

     YEAR(date_expression);
     

2. Get year from date in MySQL:

   • Description: Using the YEAR function allows you to obtain the year from a specific date.
   • Example:

     SELECT YEAR('2023-01-15') AS extracted_year;
     

3. MySQL YEAR function examples:

   • Description: Various examples of using the YEAR function in MySQL to extract years from different dates.
   • Examples:

     SELECT YEAR('2023-01-15') AS extracted_year; -- Returns 2023
     SELECT YEAR('2023-02-28') AS extracted_year; -- Returns 2023
     

4. Extracting the year from a datetime field in MySQL:

   • Description: The YEAR function can also be used to extract the year from a datetime field.
   • Example:

     SELECT YEAR('2023-01-15 14:30:00') AS extracted_year;
     

5. Handling NULL values with MySQL YEAR function:

   • Description: The YEAR function may return NULL for certain date expressions. You can handle NULL values using the COALESCE function.
   • Example:

     SELECT COALESCE(YEAR('2023-01-15'), 0) AS extracted_year;
     

6. MySQL YEAR function with date calculations:

   • Description: Using date calculations with the YEAR function allows for more complex operations.
   • Example:

     SELECT YEAR(NOW()) - YEAR('1990-01-01') AS years_since_1990;
     

7. Formatting year in MySQL queries:

   • Description: You can format the extracted year in various ways within MySQL queries.
   • Example (Formatting):

     SELECT CONCAT('Year: ', YEAR('2023-01-15')) AS formatted_result;
     

8. Extracting only the last two digits of the year in MySQL:

   • Description: If you need only the last two digits of the year, you can use the RIGHT function.
   • Example:

     SELECT RIGHT(YEAR('2023-01-15'), 2) AS last_two_digits;
     

9. MySQL YEAR vs EXTRACT differences:

   • Description: The YEAR function extracts the year from a date or datetime expression, while EXTRACT allows more flexibility by extracting various components (e.g., year, month, day) based on a specified unit.
   • Example (EXTRACT):

     SELECT EXTRACT(YEAR FROM '2023-01-15') AS extracted_year;