I have two date fields in my Netezza table and I wish to find the number of days between them. The original table I pull these two fields from has them recorded as DATE. I wish to subtract SECOND_DATE from FIRST_DATE and wish to include the magnitude i.e. negative days in this example There are -158 days between SECOND_DATE and

Calculating the number of days between dates including magnitude in Netezza

I have two date fields in my Netezza table and I wish to find the number of days between them.

The original table I pull these two fields from has them recorded as DATE.

FIRST_DATE            SECOND_DATE
2020-04-20 00:00:00 2019-11-14 00:00:00

​x
 
FIRST_DATE            SECOND_DATE2020-04-20 00:00:00 2019-11-14 00:00:00​

I wish to subtract SECOND_DATE from FIRST_DATE and wish to include the magnitude i.e. negative days in this example

There are -158 days between SECOND_DATE and FIRST_DATE

I have tried using the following but I only get the absolute number of days:

days_between(DATETIME(FIRST_FILL_DRUG_B),DATETIME(LAST_FILL_DRUG_A))

 
days_between(DATETIME(FIRST_FILL_DRUG_B),DATETIME(LAST_FILL_DRUG_A))​

Is there a way to include the magnitude?

You can try this: Select datediff(day, SECOND_DATE, FIRST_DATE) as magnitude from table1;