Db2 DATE Type

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 DATE type

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 0001 to 9999.
  • The range of the month is 1 to 12
  • 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 0001-01-01 to 9999-12-31.

The syntax for the type of a date value:

DATE
Code language: SQL (Structured Query Language) (sql)

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 nameAbbreviationDate formatExample
International Standards OrganizationISOyyyy-mm-dd2019-12-15
IBM® USA standardUSAmm/dd/yyyy12/15/2019
IBM European standardEURdd.mm.yyyy15.12.2019
Japanese industrial standard Christian eraJISyyyy-mm-dd2019-12-15
Installation-definedLOCALAny installation-defined form

Date Literals

The following string represents a date value:

'2019-05-06'
Code language: SQL (Structured Query Language) (sql)

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','2019-05-6','2019-5-06', and '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 '0800-10-20'.

Db2 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 WHERE clause:

SELECT title, published_date FROM books WHERE published_date = '2006-02-01';
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Db2 Date -querying data by date literal

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;
Code language: SQL (Structured Query Language) (sql)
Db2 Date - querying data by date literal using between operator

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) );
Code language: SQL (Structured Query Language) (sql)

The 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 reading_list table:

INSERT INTO reading_lists(user_id, book_id) VALUES (1,1);
Code language: SQL (Structured Query Language) (sql)

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 reading_lists table:

SELECT * FROM reading_lists;
Code language: SQL (Structured Query Language) (sql)
Db2 Date - default value for a date column

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');
Code language: SQL (Structured Query Language) (sql)

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 statement:

SELECT * FROM reading_lists;
Code language: SQL (Structured Query Language) (sql)
Db2 Date - insert date literal example

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 CURRENT_DATE function:

SELECT CURRENT_DATE FROM sysibm.sysdummy1;
Code language: SQL (Structured Query Language) (sql)

Or you can use the following statement:

SELECT CURRENT DATE FROM sysibm.sysdummy1;
Code language: SQL (Structured Query Language) (sql)

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 YEAR(), MONTH(), and DAY() function respectively.

SELECT YEAR(CURRENT_DATE) current_year, MONTH(CURRENT_DATE) current_month, DAY(CURRENT_DATE) current_day FROM sysibm.sysdummy1;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

4) Formatting date in various formats

The 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;
Code language: SQL (Structured Query Language) (sql)

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.

Was this tutorial helpful ?