How QUARTER() Works in MariaDB

In MariaDB, QUARTER() is a built-in date and time function that returns the quarter from a given date expression.

It accepts one argument, which is the date you want to extract the quarter from.

It returns the quarter as a number in the range 1 to 4. For dates with zero months (e.g. 0000-00-00 or 2025-00-00), the result is 0.

Syntax

The syntax goes like this:

QUARTER(date)

Where date is the date expression to get the quarter from.

Example

Here’s an example:

SELECT QUARTER('2030-08-01');

Result:

+-----------------------+
| QUARTER('2030-08-01') |
+-----------------------+
|                     3 |
+-----------------------+

Datetime Values

It also works with datetime values:

SELECT QUARTER('2030-08-01 10:30:45');

Result:

+--------------------------------+
| QUARTER('2030-08-01 10:30:45') |
+--------------------------------+
|                              3 |
+--------------------------------+

Zero Months

Dates with zero months result in 0.

Example:

SELECT QUARTER('2030-00-00');

Result:

+-----------------------+
| QUARTER('2030-00-00') |
+-----------------------+
|                     0 |
+-----------------------+

Numeric Dates

It’s also possible to pass dates as a number, as long as it makes sense as a date.

Example

SELECT QUARTER(20301125);

Result:

+-------------------+
| QUARTER(20301125) |
+-------------------+
|                 4 |
+-------------------+

Or even the following (which uses a two-digit year):

SELECT QUARTER(301125);

Result:

+-----------------+
| QUARTER(301125) |
+-----------------+
|               4 |
+-----------------+

But it must make sense as a date. Here’s what happens if I increase the day part to an invalid day:

SELECT QUARTER(20301135);

Result:

+-------------------+
| QUARTER(20301135) |
+-------------------+
|              NULL |
+-------------------+
1 row in set, 1 warning (0.000 sec)

We can check the warning like this:

SHOW WARNINGS;

Result:

+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: '20301135' |
+---------+------+--------------------------------------+

Other Delimiters

You can use other delimiters for the date. MariaDB is quite forgiving when it comes to delimiters on dates. Here are some valid examples:

SELECT 
    QUARTER('2030/06/25'),
    QUARTER('2030,06,25'),
    QUARTER('2030:06:25'),
    QUARTER('2030;06!25');

Result (using vertical output):

QUARTER('2030/06/25'): 2
QUARTER('2030,06,25'): 2
QUARTER('2030:06:25'): 2
QUARTER('2030;06!25'): 2

Current Date

We can pass NOW() as the datetime argument to use the current date:

SELECT 
    NOW(),
    QUARTER(NOW());

Result:

+---------------------+----------------+
| NOW()               | QUARTER(NOW()) |
+---------------------+----------------+
| 2021-05-18 09:23:27 |              2 |
+---------------------+----------------+

Invalid Arguments

When passed an invalid argument, QUARTER() returns null:

SELECT QUARTER('2030-65-78');

Result:

+-----------------------+
| QUARTER('2030-65-78') |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set, 1 warning (0.000 sec)

Check the warning:

SHOW WARNINGS;

Result:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2030-65-78' |
+---------+------+----------------------------------------+

Missing Argument

Calling QUARTER() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT QUARTER();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

And another example:

SELECT QUARTER('2030-12-10', '2031-12-10');

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' '2031-12-10')' at line 1