YEAR
This page is part of MariaDB's Documentation.
The parent of this page is: Data Types for MariaDB Xpand
Topics on this page:
Overview
Four-digit year.
USAGE
YEAR[(4)]
DETAILS
Data Type | Minimum Value | Maximum Value |
|---|---|---|
|
|
|
EXAMPLES
Formats
MariaDB Xpand supports YEAR and YEAR(4), but does not support YEAR(2). However, Xpand can accept 2-digit values as input:
CREATE TABLE year_format_example (
description VARCHAR(30),
example YEAR
);
INSERT INTO year_format_example VALUES
('4-digit numeric year', 1969),
('2-digit numeric year', 69),
('4-digit string year', '1970'),
('2-digit string year', '70');
2-digit year values in the range 70 - 99 become 1970 - 1999, and 2-digit year values in the range 00 - 69 become 2000 - 2069:
SELECT * FROM year_format_example;
+----------------------+---------+
| description | example |
+----------------------+---------+
| 4-digit numeric year | 1969 |
| 2-digit numeric year | 2069 |
| 4-digit string year | 1970 |
| 2-digit string year | 1970 |
+----------------------+---------+
Since 2-digit year values are non-specific, MariaDB recommends using 4-digit year values.
Range
Xpand replaces values outside the supported range with 0000:
CREATE TABLE year_range_example (
description VARCHAR(30),
example YEAR
);
INSERT INTO year_range_example VALUES
('minimum', 1901),
('maximum', 2155),
('below minimum', 1900),
('above maximum', 2156);
The resulting data would look like this:
SELECT * FROM year_range_example;
+---------------+---------+
| description | example |
+---------------+---------+
| minimum | 1901 |
| maximum | 2155 |
| below minimum | 0000 |
| above maximum | 0000 |
+---------------+---------+
Year Zero
Xpand supports 1- to 4-digits formats for year zero:
CREATE TABLE year_zero_example (
description VARCHAR(30),
example YEAR
);
INSERT INTO year_zero_example VALUES
('4-digit numeric zero', 0000),
('3-digit numeric zero', 000),
('2-digit numeric zero', 00),
('1-digit numeric zero', 0),
('4-digit string zero', '0000'),
('3-digit string zero', '000'),
('2-digit string zero', '00'),
('1-digit string zero', '0');
The resulting data would look like this:
SELECT * FROM year_zero_example;
+----------------------+---------+
| description | example |
+----------------------+---------+
| 4-digit numeric zero | 0000 |
| 3-digit numeric zero | 0000 |
| 2-digit numeric zero | 0000 |
| 1-digit numeric zero | 0000 |
| 4-digit string zero | 2000 |
| 3-digit string zero | 2000 |
| 2-digit string zero | 2000 |
| 1-digit string zero | 2000 |
+----------------------+---------+
Two-digit Year
Xpand does not support a 2-digit YEAR column:
CREATE TABLE year_2_fails (
example YEAR(2)
);
ERROR 1 (HY000): [32770] invalid parameter value: Supports only YEAR or YEAR(4) column.