YEAR

USAGE

YEAR[(4)]

DETAILS

Data Type

Minimum Value

Maximum Value

YEAR

1901

2155

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

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.

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES