How to Extract DATE and TIME from TIMESTAMP: DB2

Extracting the date and time independently from a timestamp is an easy way. In DB2, you can extract the date and time from a timestamp using SQL functions. Here’s how you can do it.

Table of contents

1. Extract Date from Current Timestamp
2. Extract Time from Current Timestamp
3. DATE and TIME Calculation
4. Extract Function
5. References

Get Srinimf’s E-books for Complex SQL queries, DB2 tough interview questions and more.

Extract Date from Current Timestamp

SELECT DATE(CURRENT TIMESTAMP) AS Date
FROM sysibm.sysdummy1;

Extract Time from Current Timestamp

SELECT TIME(CURRENT TIMESTAMP) AS Time
FROM sysibm.sysdummy1;

Top UDEMY Courses

DATE and TIME Calculation

current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS 
current time + 5 HOURS - 3 MINUTES + 10 SECONDS

These SQL queries will help you extract and manipulate the date and time from a timestamp in DB2.

Extract Function

In DB2, the EXTRACT function is to extract specific components of a date or timestamp value. Here’s an example of how to use the EXTRACT function in a DB2 SQL query.

To extract the year from a date or timestamp:

SELECT EXTRACT(YEAR FROM my_date_column) AS Year
FROM my_table;

To extract the month from a date or timestamp:

SELECT EXTRACT(MONTH FROM my_date_column) AS Month
FROM my_table;

To extract the day from a date or timestamp:

SELECT EXTRACT(DAY FROM my_date_column) AS Day
FROM my_table;

To extract the hour from a timestamp:

SELECT EXTRACT(HOUR FROM my_timestamp_column) AS Hour
FROM my_table;

To extract the minute from a timestamp:

SELECT EXTRACT(MINUTE FROM my_timestamp_column) AS Minute
FROM my_table;

To extract the second from a timestamp:

SELECT EXTRACT(SECOND FROM my_timestamp_column) AS Second
FROM my_table;

Extract Date

SELECT EXTRACT(DATE FROM my_date_or_timestamp_column) AS Date

FROM my_table;

References

• DB2 Extract function examples