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:

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:

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)

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)

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)

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.