How the DAYOFYEAR() function works in Mariadb?

The DAYOFYEAR() function is a date and time function that returns the day of the year for a given date.

Posted on

The DAYOFYEAR() function is a date and time function that returns the day of the year for a given date. The return value is an integer from 1 to 366, where 1 represents January 1, 2 represents January 2, and so on. The DAYOFYEAR() function can be used to perform various calculations and operations based on the day of the year.

Syntax

The syntax of the DAYOFYEAR() function is as follows:

DAYOFYEAR(date)

The date parameter can be any valid date or datetime expression that can be converted to a date. If the date parameter is invalid or null, the function returns null.

Examples

Here are some examples of using the DAYOFYEAR() function in Mariadb.

Example 1: Get the day of the year for a specific date

The following query uses the DAYOFYEAR() function to get the day of the year for the date ‘2024-01-14’.

SELECT DAYOFYEAR('2024-01-14') AS day_of_year;

The output is:

+------------+
| day_of_year|
+------------+
|         14 |
+------------+

This means that the date ‘2024-01-14’ is the 14th day of the year.

Example 2: Get the number of days between two dates

The following query uses the DAYOFYEAR() function along with the DATEDIFF() function to get the number of days between the dates ‘2024-01-14’ and ‘2024-02-14’.

SELECT DATEDIFF('2024-02-14', '2024-01-14') AS days_between;

The output is:

+--------------+
| days_between |
+--------------+
|           31 |
+--------------+

This means that there are 31 days between the dates ‘2024-01-14’ and ‘2024-02-14’.

Alternatively, you can also use the DAYOFYEAR() function to calculate the difference as follows:

SELECT DAYOFYEAR('2024-02-14') - DAYOFYEAR('2024-01-14') AS days_between;

The output is the same as the previous query.

Example 3: Get the number of days left in the year for a specific date

The following query uses the DAYOFYEAR() function along with the LEAP() function and the CASE expression to get the number of days left in the year for the date ‘2024-01-14’.

SELECT CASE WHEN LEAP('2024-01-14') = 1 THEN 366 - DAYOFYEAR('2024-01-14')
            ELSE 365 - DAYOFYEAR('2024-01-14')
       END AS days_left;

The output is:

+----------+
| days_left|
+----------+
|      352 |
+----------+

This means that there are 352 days left in the year for the date ‘2024-01-14’.

The LEAP() function returns 1 if the year of the given date is a leap year, and 0 otherwise. The CASE expression determines whether to subtract the DAYOFYEAR() value from 366 or 365, depending on the leap year status.

Example 4: Get the date for a specific day of the year

The following query uses the DAYOFYEAR() function along with the DATE_ADD() function to get the date for the 100th day of the year 2024.

SELECT DATE_ADD('2024-01-01', INTERVAL DAYOFYEAR('2024-01-01') + 99 DAY) AS date;

The output is:

+------------+
| date       |
+------------+
| 2024-04-09 |
+------------+

This means that the 100th day of the year 2024 is April 9.

The DATE_ADD() function adds a specified interval to a given date. The DAYOFYEAR() function returns the day of the year for the date ‘2024-01-01’, which is 1. Adding 99 to this value gives the desired day of the year, which is 100.

Example 5: Get the quarter of the year for a specific date

The following query uses the DAYOFYEAR() function along with the FLOOR() function and the CASE expression to get the quarter of the year for the date ‘2024-01-14’.

SELECT CASE WHEN FLOOR((DAYOFYEAR('2024-01-14') - 1) / 91) = 0 THEN 1
            WHEN FLOOR((DAYOFYEAR('2024-01-14') - 1) / 91) = 1 THEN 2
            WHEN FLOOR((DAYOFYEAR('2024-01-14') - 1) / 91) = 2 THEN 3
            ELSE 4
       END AS quarter;

The output is:

+---------+
| quarter |
+---------+
|       1 |
+---------+

This means that the date ‘2024-01-14’ is in the first quarter of the year.

The FLOOR() function returns the largest integer value that is less than or equal to a given number. The CASE expression assigns a value of 1, 2, 3, or 4 to the quarter, depending on the result of the FLOOR() function. The DAYOFYEAR() function returns the day of the year for the date ‘2024-01-14’, which is 14. Subtracting 1 from this value and dividing it by 91 gives the number of complete quarters that have passed before the date. In this case, the result is 0, which means that the date is in the first quarter.

There are some other functions that are related to the DAYOFYEAR() function in Mariadb. Here are some of them:

  • The DAY() function returns the day of the month for a given date, where 1 represents the first day of the month, 2 represents the second day of the month, and so on. For example, DAY('2024-01-14') returns 14, which means that the date ‘2024-01-14’ is the 14th day of the month.
  • The DAYOFWEEK() function returns the day of the week for a given date, where 1 represents Sunday, 2 represents Monday, and so on. For example, DAYOFWEEK('2024-01-14') returns 1, which means that the date ‘2024-01-14’ is a Sunday.
  • The DAYNAME() function returns the name of the day of the week for a given date, based on the current language setting. For example, DAYNAME('2024-01-14') returns ‘Sunday’, which means that the date ‘2024-01-14’ is a Sunday.

Conclusion

The DAYOFYEAR() function is a useful function that can help you perform various calculations and operations based on the day of the year for a given date. You can use it along with other date and time functions to manipulate and format dates in Mariadb. I hope this article has helped you understand how the DAYOFYEAR() function works in Mariadb.