Some SQL Date/Time features

SQL Timestamp

The DB2 TIMESTAMP is a temporal data type that holds the combination of date and time. The format of a TIMESTAMP is YYYY-MM-DD-HH.MM.SS.MMMMMM which is fixed at 25 characters.

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 
2020-05-13-13.53.48.741951    

Some common SQL timestamp functions

Use functions Year, Month, Day etc.. to extract parts of the timestamp

SELECT
    YEAR (CURRENT TIMESTAMP) CURRENT_YEAR,
    MONTH (CURRENT TIMESTAMP) CURRENT_MONTH,
	QUARTER (CURRENT TIMESTAMP) CURRENT_QUARTER,
    DAY (CURRENT TIMESTAMP) CURRENT_DAY,
    HOUR (CURRENT TIMESTAMP) CURRENT_HOUR,
    MINUTE (CURRENT TIMESTAMP) CURRENT_MINUTE,
    SECOND (CURRENT TIMESTAMP) CURRENT_SECOND,
    MICROSECOND (CURRENT TIMESTAMP) CURRENT_MICROSECOND
FROM SYSIBM.SYSDUMMY1 

Result:

  CURRENT_YEAR   CURRENT_MONTH   CURRENT_QUARTER    CURRENT_DAY    CURRENT_HOUR    CURRENT_MINUTE   CURRENT_SECOND   CURRENT_MICROSECOND
        2.024               6                2              20              20           34               16            920.602   

Using QUARTER to generate a quarterly sales report

SELECT YEAR(Order_Date)||'-Q'||QUARTER(ORDER_DATE) AS Sales_Qtr,   
   SUM(ORDER_VALUE) AS Sales_Amount                              
FROM ORDER_HED                                                     
GROUP BY YEAR(ORDER_DATE)||'-Q'||QUARTER(ORDER_DATE)                 
ORDER BY Sales_Qtr                                                   

 SALES_QTR                                              SALES_AMOUNT   
 2016-Q1                                                      230,00   
 2016-Q2                                                      420,00   
 2016-Q3                                                      330,00   
 2016-Q4                                                      180,00   
 ********  End of data  ********                                       

Some more date/time functions

SELECT
	DAYOFMONTH (CURRENT TIMESTAMP),
	DAYOFWEEK (CURRENT TIMESTAMP),
	DAYOFWEEK_ISO (CURRENT TIMESTAMP),
	DAYOFYEAR (CURRENT TIMESTAMP)
FROM SYSIBM.SYSDUMMY1 

Result:

  DAYOFMONTH       DAYOFWEEK   DAYOFWEEK_ISO       DAYOFYEAR           
          13               4               3             134            

VARCHAR_FORMAT

The VARCHAR_FORMAT function returns a character representation of an input expression.

SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDD') AS Format_YYYYMMDD FROM SYSIBM.SYSDUMMY1 

Result:

FORMAT_YYYYMMDD       
20200513              

SELECT VARCHAR_FORMAT(1234.56,'S$9,999.99') AS Money_Format FROM SYSIBM.SYSDUMMY1    

Result:

MONEY_FORMAT    
 +$1,234.56      

Using LAST_DAY function to get first and Last day of the month

The LAST_DAY scalar function returns a date or timestamp value that represents the last day of the month of the argument. Unfortunetely there is no FIRST_DAY function in DB2/400, but with a little date math we can improvise.

SELECT VARCHAR_FORMAT(CHAR(LAST_DAY(CURRENT DATE)),'YYYYMMDD') AS LAST_DAY FROM SYSIBM.SYSDUMMY1 

Result:

LAST_DAY      
20200531      

SELECT VARCHAR_FORMAT(CHAR(LAST_DAY(CURRENT DATE) + 1 day - 1 month),'YYYYMMDD') AS FIRST_DAY FROM SYSIBM.SYSDUMMY1 
 

Result:

FIRST_DAY    
20200501