MySQL Tutorial/Data Types/YEAR
Содержание
Four-digit year
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date YEAR,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "1995", "20930725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "1976", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "1978", "19920315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "1982", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "1984", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "1987", "19940104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "1995", "19970212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "1996", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1995 | 2093-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978 | 1992-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987 | 1994-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1995 | 1997-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> SELECT * FROM employee WHERE (start_date = 1995);
+------+------------+-----------+------------+------------+---------+----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+----------+-------------+
| 1 | Jason | Martin | 1995 | 2093-07-25 | 1234.56 | Toronto | Programmer |
| 7 | David | Larry | 1995 | 1997-02-12 | 7897.78 | New York | Manager |
+------+------------+-----------+------------+------------+---------+----------+-------------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
Insert value to a YEAR type column
mysql>
mysql> CREATE TABLE myTable(
-> ID TINYINT UNSIGNED NOT NULL DEFAULT 1,
-> Copyright YEAR,
-> Name VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO myTable (ID, Copyright, Name)VALUES (1,1994,"A");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from myTable;
+----+-----------+------+
| ID | Copyright | Name |
+----+-----------+------+
| 1 | 1994 | A |
+----+-----------+------+
1 row in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
To determine how many years served
YEAR() pulls out the year part of a date.
RIGHT() pulls off the rightmost five characters that represent the MM-DD (calendar year) part of the date.
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.01 sec)
mysql>
mysql>
mysql>
mysql>
mysql> SELECT first_name, start_date, CURDATE(),
-> (YEAR(CURDATE())-YEAR(start_date)) - (RIGHT(CURDATE(),5)<RIGHT(start_date,5))
-> AS YearServed
-> FROM employee;
+------------+------------+------------+------------+
| first_name | start_date | CURDATE() | YearServed |
+------------+------------+------------+------------+
| Jason | 1996-07-25 | 2007-07-22 | 10 |
| Alison | 1976-03-21 | 2007-07-22 | 31 |
| James | 1978-12-12 | 2007-07-22 | 28 |
| Celia | 1982-10-24 | 2007-07-22 | 24 |
| Robert | 1984-01-15 | 2007-07-22 | 23 |
| Linda | 1987-07-30 | 2007-07-22 | 19 |
| David | 1990-12-31 | 2007-07-22 | 16 |
| James | 1996-09-17 | 2007-07-22 | 10 |
+------------+------------+------------+------------+
8 rows in set (0.02 sec)
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
Two-digit year
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date YEAR,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "1995", "20930725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "1976", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "1978", "19920315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "1982", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "1984", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "1987", "19940104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "1995", "19970212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "1996", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1995 | 2093-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978 | 1992-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987 | 1994-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1995 | 1997-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> SELECT * FROM employee WHERE (start_date = 95);
+------+------------+-----------+------------+------------+---------+----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+----------+-------------+
| 1 | Jason | Martin | 1995 | 2093-07-25 | 1234.56 | Toronto | Programmer |
| 7 | David | Larry | 1995 | 1997-02-12 | 7897.78 | New York | Manager |
+------+------------+-----------+------------+------------+---------+----------+-------------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.01 sec)
mysql>
YEAR
YEAR records the year and occupies just 1 byte of data.
YEAR values can range from 1901 to 2155.
You can specify a year in either string or numeric notation.
YEAR values can be specified in either two- or four-digit form.
Two-digit years are converted to four digits.
Two-digit years in the range 1 to 69 (or "0" to "69") will be taken as years from 2001 through 2069.
Years in the range 70 to 99 will be interpreted as being in the range 1970 through 1999.
10. 26. YEAR 10. 26. 1. YEAR 10. 26. 2. <A href="/Tutorial/MySQL/0200__Data-Types/YEARcolumntype.htm">YEAR column type</a> 10. 26. 3. <A href="/Tutorial/MySQL/0200__Data-Types/InsertvaluetoaYEARtypecolumn.htm">Insert value to a YEAR type column</a> 10. 26. 4. <A href="/Tutorial/MySQL/0200__Data-Types/Twodigityear.htm">Two-digit year</a> 10. 26. 5. <A href="/Tutorial/MySQL/0200__Data-Types/Fourdigityear.htm">Four-digit year</a> 10. 26. 6. <A href="/Tutorial/MySQL/0200__Data-Types/Todeterminehowmanyyearsserved.htm">To determine how many years served</a>
YEAR column type
mysql>
mysql>
mysql> CREATE TABLE myTable
-> (
-> ID SMALLINT UNSIGNED,
-> Copyright YEAR,
-> OrderDate TIMESTAMP
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> desc myTable;
+-----------+----------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+-------------------+-------+
| ID | smallint(5) unsigned | YES | | NULL | |
| Copyright | year(4) | YES | | NULL | |
| OrderDate | timestamp | NO | | CURRENT_TIMESTAMP | |
+-----------+----------------------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)