Some SQL Date/Time features

SQL Timestamp

The DB2 TIMESTAMP is a data type that holds the combination of date and time. The format of a TIMESTAMP is YYYY-MM-DD-HH.MM.SS.MMMMMM and uses 26 characters.

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 

Some common SQL date/time functions

CURRENT TIMESTAMP (or CURRENT_TIMESTAMP) is a special register that provides current system timestamp.

Use SQL built-in functions YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND... to extract parts of a timestamp

SELECT
    YEAR (CURRENT TIMESTAMP) AS CURRENT_YEAR,
    MONTH (CURRENT TIMESTAMP) AS CURRENT_MONTH,
    QUARTER (CURRENT TIMESTAMP) AS CURRENT_QUARTER,
    DAY (CURRENT TIMESTAMP) AS CURRENT_DAY,
    HOUR (CURRENT TIMESTAMP) AS CURRENT_HOUR,
    MINUTE (CURRENT TIMESTAMP) AS CURRENT_MINUTE,
    SECOND (CURRENT TIMESTAMP) AS CURRENT_SECOND,
    MICROSECOND (CURRENT TIMESTAMP) AS CURRENT_MICROSECOND,
    CURRENT DATE, CURRENT TIME		
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   

Some other less common SQL Date/Time functions

SELECT
WEEK(CURRENT TIMESTAMP) AS WEEK_OF_YEAR,	
WEEK_ISO(CURRENT TIMESTAMP) AS WEEK_OF_YEAR_ISO,	
QUARTER(CURRENT TIMESTAMP) AS QUARTER_OF_YEAR,	
MIDNIGHT_SECONDS(CURRENT TIMESTAMP) AS SECONDS_SINCE_MIDNIGHT
FROM SYSIBM.SYSDUMMY1 

Result:

 WEEK_OF_YEAR   WEEK_OF_YEAR_ISO  QUARTER_OF_YEAR  SECONDS_SINCE_MIDNIGHT   
            24                24                2              65.970     
 ********  End of data  ********                                          

Extract parts of a date: DAYOFMONTH DAYOFWEEK, DAYOFWEEK_ISO, DAYOFYEAR

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               4             134            

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    

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  ********                                       

VARCHAR_FORMAT

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

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

Result:

CURRENT TIMESTAMP           FORMAT_YYYYMMDD           
2020-05-31-19.39.07.454149  20200531                  

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