MySQL Date and Time Exercises: Get the last day of the current year

MySQL Date Time: Exercise-5 with Solution

Write a MySQL query to get the last day of the current year.

Code:

-- This SQL query creates a date using the year extracted from the current date, the month '12', and the day '31'.

SELECT 
    STR_TO_DATE( -- Converts a string into a date value.
CONCAT( -- Concatenates multiple strings into one string.
            12, -- Specifies the month as '12'.
            31, -- Specifies the day as '31'.
EXTRACT(YEAR FROM CURDATE()) -- Extracts the year from the current date (CURDATE()).
        ), 
        '%m%d%Y' -- Specifies the format of the input string.
    );

Explanation:

• The EXTRACT(YEAR FROM CURDATE()) function extracts the year from the current date using the CURDATE() function.
• The CONCAT() function concatenates '12' (month), '31' (day), and the extracted year to form a string representing December 31 of the current year.
• %m%d%Y represents the format for the input string: %m for the month, %d for the day, and %Y for the year.
• STR_TO_DATE() converts the concatenated string into a date value based on the specified format.
• This effectively creates a date for December 31 of the current year.

Sample Output:

STR_TO_DATE(CONCAT(12,31, EXTRACT(YEAR FROM CURDATE())), '%m%d%Y')
2017-12-31T05:00:00.000Z

MySQL Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to get the first day of the current year.
Next:Write a MySQL query to calculate the age in year.