# 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.

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.

## Related Functions

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.