CODEWITHSUNDEEP
sqldateteradata
K.Dharma
K.Dharma

Reputation: 39

Date diff in Teradata

I'm trying to do date difference between two dim_ck fields. If it's < 30, then I need to set a flag Y, else N.

Steps followed:

As it's a dim_ck, I converted it to Date from dim_ck. There is one value with -3, so it didn't allow me to convert, so I tried to set a default date to -3 and then was able to convert it to Date. The below query returns Y and N result in Terdata DB, but it's not taking the date difference correctly.

For ex: 1998/07/31 - 2015/10/01 = Y, it's actually > 30, so it should return N. Please help.

Query:

SELECT date_dim_ck, 
   end_date_dim_ck, 
   CASE 
     WHEN( CASE 
       WHEN date_dim_ck = -3 THEN (To_date('01/01/1753', 'mm/dd/yyyy')) 
          ELSE Cast(( date_dim_ck - 19000000 ) AS DATE) 
     END - ( Cast (( end_date_dim_ck - 19000000 ) AS DATE) ) ) < 30 
     THEN 'Y' 
       ELSE 'N' 
     END AS FLAG 
   FROM table

Upvotes: 0

Views: 12556

Answers (1)

dnoeth
dnoeth

Reputation: 60462

#1: You didn't convert SQL Server's DATEDIFF (day, date_dim_ck, end_date_dim_ck) correctly.

SELECT DATE '1998-07-31' - DATE '2015-10-01' returns -6217 which is way less than 30 :-)

#2: There's no need for applying TO_DATE plus a format, Teradata supports Standard SQL date literals, DATE 'YYYY-MM-DD'.

CASE 
  WHEN Cast (( end_date_dim_ck - 19000000 ) AS DATE)  -   
    CASE 
      WHEN date_dim_ck = -3
        THEN DATE '1753-01-01'
        ELSE Cast(( date_dim_ck - 19000000 ) AS DATE) 
    END < 30 
  THEN 'Y' 
    ELSE 'N' 
END AS FLAG

Assuming that 1753-01-01 actually doesn't exist within the data:

CASE 
  WHEN Cast ((end_date_dim_ck - 19000000 ) AS DATE)
     - Cast((NullIf(date_dim_ck,-3) - 19000000 ) AS DATE) < 30 
  THEN 'Y' 
  ELSE 'N' 
END AS FLAG

Btw, the lowest date Teradat supports is DATE '0001-01-01'.

Upvotes: 2

Related Questions