SQL DAYS scalar function in DB2 for i SQL

SQL DAYS scalar function in DB2 for i SQL

DAYS

The DAYS function returns an integer representation of a date. The result is 1 more than the number of days from January 1, 0001 to passed date.

Syntax

DAYS(expression)

The argument should be an expression that returns either date, timestamp, character string, graphic string data type. The return value is the large integer. Also, the result can be NULL if the argument value is null.

Example#1:

If the argument is a string.

SELECT  DAYS('2024-03-16') FROM sysibm.sysdummy1

16th March 2024 is (Jan 31 + 29 Feb + 16th of March. Therefore, returns 76.

         DAYS
      738,961

Just to confirm whether we get the right result, just use DATE function on the DAYS value 73861.

SELECT  DATE(738961) FROM sysibm.sysdummy1

DATE ( 738961 )
   03/16/24    

Or

SELECT DAYS('2024-03-16-15.10.40.456465') FROM sysibm.sysdummy1

         DAYS
      738,961

Example#2:

The result can be null if the argument is null.

Create a table having date field and that allows null value to be inserted.

CREATE TABLE QTEMP/DT (DATE1 DATE )

INSERT INTO QTEMP/DT VALUES(null)

Output

DATE1
-    

select DAYS(date1) from qtemp/dt

DAYS ( DATE1 ) 
              -

Returns NULL value as date1 field has NULL value in table qtemp/dt.

Example#3:

If the argument is a timestamp, the result is the date part of the timestamp.

SELECT  DAYS(current timestamp) FROM sysibm.sysdummy1

Or

SELECT  DAYS(current_timestamp) FROM sysibm.sysdummy1

         DAYS
      738,961

Example#4:

Argument value is the character string '2024076', which represents a date in the format yyyynnn, where yyyy is the year, and nnn is the day of the year.

SELECT   DAYS('2024076') FROM sysibm.sysdummy1

DAYS ( '2024076' )
         738,961