AppLib DAYS DIFF

From Eigenpedia

Contents 1 Syntax 2 Purpose 3 Parameters 4 Example 5 Source Code

Syntax

APPLIB.DAYS_DIFF ( date_expression1, date_expression2 )

APPLIB.DAYS_DIFF ( timestamp_expression1, timestamp_expression2 )

Purpose

Returns the differents in units of days between two dates or two timestamps (ie. exp1 - exp2). The return value is of type BIGINT. Note that differences of less than 24 hours will not count as a day

Parameters

• date_expression1: date to subtract from [DATE]
• date_expression2: date to be subtracted [DATE]
• timestamp_expression1: timestamp to subtract from [TIMESTAMP]
• timestamp_expression2: timestamp to be subtracted [TIMESTAMP]

Example

VALUES( APPLIB.DAYS_DIFF(DATE'2010-2-20', DATE'2010-3-10'),
    APPLIB.DAYS_DIFF(TIMESTAMP'2006-10-4 10:00:00', 
    TIMESTAMP'2006-10-4 10:21:00') )

RETURNS:

APPLIB.DAYS_DIFF(DATE'2010-2-20', DATE'2010-3-10')	APPLIB.DAYS_DIFF(TIMESTAMP'2006-10-4 10:00:00', TIMESTAMP'2006-10-4 10:21:00')
-18	0

Source Code

create or replace function applib.days_diff(d1 date, d2 date)
returns bigint
specific days_diff_dates
deterministic
contains sql
return (
  extract( day from ((d1 - d2) day) )
);

create or replace function applib.days_diff(ts1 timestamp, ts2 timestamp)
returns bigint
specific days_diff_timestamps
deterministic
contains sql
return (
  extract( day from ((ts1 - ts2) day) )
);