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