Summary: in this tutorial, you will learn how to use the Db2
DATE type and how to use it to store dates in the database.
Introduction to the Db2
In Db2, a date represents a point in time using the Gregorian calendar. A date consists of three parts: year, month, and day.
- The range of the year is from
- The range of the month is
- The range of the day is 1 to 28, 29, 30 or 31, depending on the month and year.
In other words, the range of a date value is from
The syntax for the type of a date value:
Internally, Db2 uses 4 bytes to represent a date value. The first two bytes represent the year, the third byte represents the month, and the last byte represents the day. Each byte stores two packed decimal digits.
Db2 stores date data in a special internal format. For displaying, Db2 converts the internal format to one of the following formats:
|Format name||Abbreviation||Date format||Example|
|International Standards Organization||ISO||yyyy-mm-dd||2019-12-15|
|IBM® USA standard||USA||mm/dd/yyyy||12/15/2019|
|IBM European standard||EUR||dd.mm.yyyy||15.12.2019|
|Japanese industrial standard Christian era||JIS||yyyy-mm-dd||2019-12-15|
|Installation-defined||LOCAL||Any installation-defined form|
The following string represents a date value:
In this format, the year is 2019, the month is 05 (May), and the day is 06.
A date literal must conform to the following rules:
- A date literal cannot have leading blank but can have trailing blanks so
' 2019-05-06'is an invalid date while
'2019-05-06 'is a valid date.
- A date literal can omit the leading zero from the month and day of the date, for example,
'2019-05-06'represent the same date.
- A date literal must include leading zeros in the year element e.g., you cannot have a date literal like
'80-10-20', it must be
DATE type examples
We’ll use the
books table from the sample database to demonstrate the date type.
1) Using date literal examples
This example finds all books published on Feb 1st, 2006. It compares the published date with a date literal in the
SELECT title, published_date FROM books WHERE published_date = '2006-02-01';
Here is the output:
The following statement finds all books published between
Feb 1 2006 and
Feb 28 2006:
SELECT published_date, title FROM books WHERE published_date BETWEEN '2006-02-01' AND '2006-02-28' ORDER BY published_date;
2) Creating a table with a date column
First, create a table to store the reading list of users:
CREATE TABLE reading_lists( user_id INT NOT NULL, book_id INT NOT NULL, added_on DATE DEFAULT CURRENT_DATE, PRIMARY KEY(user_id, book_id) );
reading_lists table has the
added_on column which is a
DATE column whose default value is the current date.
Next, insert a new row into the
INSERT INTO reading_lists(user_id, book_id) VALUES (1,1);
Because we didn’t supply a value to the
added_on column, it took the default value which is the current date.
Then, query data from the
SELECT * FROM reading_lists;
After that, insert a new row into the
reading_lists table, but provide the
addon_date column with a date literal:
INSERT INTO reading_lists(user_id, book_id, added_on) VALUES (2,2,'2019-5-3');
Note that we didn’t use the leading zero in the month and day element of the date literal.
Finally, verify the content of the
reading_lists table by using the following
SELECT * FROM reading_lists;
Common Db2 date functions
Let’s quickly examine some common date functions.
1) Getting the current date
To get the current date of the operating system on which the Db2 instance is running, you use the
SELECT CURRENT_DATE FROM sysibm.sysdummy1;
Or you can use the following statement:
SELECT CURRENT DATE FROM sysibm.sysdummy1;
Note that the
sysdummy1 is a catalog table that consists of one row. The
sysdummy1 resides in the
sysibm schema. It is used in the SQL statement that requires a table reference.
2) Extracting the year, month, and day from a date
To extract the date parts such as the year, month, and day from a date, you use the
DAY() function respectively.
SELECT YEAR(CURRENT_DATE) current_year, MONTH(CURRENT_DATE) current_month, DAY(CURRENT_DATE) current_day FROM sysibm.sysdummy1;
3) Extracting the date from a timestamp
The extract the date from a timestamp, you use the
DATE() function. The following example returns the date from the current timestamp:
SELECT DATE (CURRENT_TIMESTAMP) FROM sysibm.sysdummy1;
4) Formatting date in various formats
CHAR() function formats a date in a specified format. For example, the following statement returns the book title and published date in ISO, USA, EUR, and JIS formats:
SELECT title, CHAR(published_date,ISO) published_date_iso, CHAR(published_date,USA) published_date_usa, CHAR(published_date,EUR) published_date_eur, CHAR(published_date,JIS) published_date_jis FROM books; ORDER BY title;
Here is the output:
In this tutorial, you have learned how to use the Db2
DATE type and how to store the dates in the tables.