SQL EXTRACT Function: How to Get Month, Day, Year

Here’re sample SQL queries to extract Month, Year and Day from the Date.

Here’s SQL Query to get System Date

You can get system date using sysibm.sysdummy1. Those are current date current time and current time zone you can get from sysibm.sysdummy1.

Extract Date and Time

You can use extract command to extract date, day, month portion from Timestamp. This extract command you can use in WHERE or ORDER BY clause.

SELECT ORDER_DATE, ORDER_TOTAL
FROM ORDER_TABLE
WHERE EXTRACT(MONTH FROM ORDER_DATE)=9;

SELECT ORDER_TOTAL
FROM ORDER_TABLE
WHERE YEAR(ORDER_DATE)=2000;

Here’s how to convert Date to Char

TO_CHAR(ORDER_DATE,YY-MM-DD)
This function converts the Order date to Char date.

Here’s how to find last day of month

SELECT ORDER_DATE, 
DATE(SUBSTR
(CHAR(ORDER_DATE),1,8) ||
'01' + 1 MONTH - 1 DAY 
AS LAST_DAY_OF_MONTH
FROM ORDER_TABLE;

DB2 Limitation on Calculating TimeStamp Differences

When you calculate differences of Timestamp, it does not consider leaf year. Always DB2 consider, 30 days per month

Differences of DATE and Time

You can find examples to find differences for DATE and TIME for your project, this is my post on it useful for you to refer.

Related Posts