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,
    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_DAY    CURRENT_HOUR   CURRENT_MINUTE   CURRENT_SECOND   CURRENT_MICROSECOND 
        2,020               5              13              14               18               56            205,472    

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