Least/Greatest Function with Dates

Database

Least/Greatest Function with Dates

CREATE VOLATILE TABLE dt AS (

SEL
CURRENT_DATE AS dt1
, CURRENT_DATE-INTERVAL '30' DAY AS dt2
, CURRENT_DATE-INTERVAL '60' DAY AS dt3

)WITH DATA PRIMARY INDEX(dt1, dt2, dt3)
 ON COMMIT PRESERVE ROWS;
 
SEL GREATEST(dt1, dt2, dt3) FROM dt;

Does the  least/greatest functions not work with dates?  I can run it with integers but get an invalid number/type of paramaters error when using dates.  Here's an example.

14 REPLIES
Junior Contributor

Re: Least/Greatest Function with Dates

Yep, this stupid function only works with numeric & character columns.

Just don't ask me why, the first time I needed it was on dates, too :-)

At least there's a workaround:

CAST(GREATEST(CAST(date1 AS INT)
,CAST(date2 AS INT)
,CAST(date3 AS INT)
) AS DATE)

Hopefully there are no NULLs, otherwise it get's quite ugly with lots of COALESCE... 

Junior Supporter

Re: Least/Greatest Function with Dates

Hi.

CASTing is your friend.

SELECT GREATEST(dt1 (INTEGER), dt2(INTEGER), dt3(INTEGER))(DATE);

HTH.

Cheers.

Carlos.

Re: Least/Greatest Function with Dates

excellent - thanks!

Enthusiast

Re: Least/Greatest Function with Dates

Thank God i lived to see master Dieter say dont 'ask me why' :)

In life nothing is impossible!

Enthusiast

Re: Least/Greatest Function with Dates

Hi, Dieter,

I have NULLS.  :-(

Can you give a small example of how to get a non-null result when using GREATEST with dates?

thanks much

Enthusiast

Re: Least/Greatest Function with Dates

you mean exclude null dates? ..where clause to be used?

Enthusiast

Re: Least/Greatest Function with Dates

no...,

I have 4 columns of dates, one of which is occasionally NULL, and I would like to get a non-null result.  It looks like if one is null, then greatest returns null:

I would think that it would take '2014-04-01' but it looks like null trumps all other values.  Dieter says use COALESCE, which I would guess means nest COALESCE inside GREATEST, or GREATEST inside COALESCE?

create table sandbox_cd.lolo
(date1 date,
date2 date,
date3 date,
date4 date)
;
insert into sandbox_cd.lolo values
('2014-04-01','2013-12-25',NULL,'2012-07-04');

select date1, date2, date3, date4,
cast(greatest(cast(date1 AS INT)
,cast(date2 AS INT)
,cast(date3 AS INT)
,cast(date3 AS INT)) as date) as maxdate
from sandbox_cd.lolo
;

the result, maxdate, is NULL

Re: Least/Greatest Function with Dates

select date1, date2, date3, date4,
cast(greatest
(
cast(COALESCE(date1, date '1900-01-01') AS INT),
cast( COALESCE(date2, date '1900-01-01') AS INT),
cast( COALESCE(date3, date '1900-01-01') AS INT),
cast( COALESCE(date4, date '1900-01-01') AS INT)
)as date) as maxdate
from lolo
;

Hi, try with above code to get the MAX date, with COALESCE I am defaulting NULL values to the minimum date we can store in teradata.

Enthusiast

Re: Least/Greatest Function with Dates

I get it.  Thanks!