All Forums Database
christophjones 3 posts Joined 01/12
15 Oct 2015
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.
 
 

dnoeth 4628 posts Joined 11/04
15 Oct 2015

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... 

Dieter

CarlosAL 512 posts Joined 04/08
15 Oct 2015

Hi.
CASTing is your friend.
SELECT GREATEST(dt1 (INTEGER), dt2(INTEGER), dt3(INTEGER))(DATE);
HTH.
Cheers.
Carlos.
 

christophjones 3 posts Joined 01/12
15 Oct 2015

excellent - thanks!

amadu 14 posts Joined 09/14
21 Oct 2015

Thank God i lived to see master Dieter say dont 'ask me why' :)
In life nothing is impossible!

Amadu Barrie

cdorger 6 posts Joined 10/15
06 Jun 2016

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

yuvaevergreen 93 posts Joined 07/09
07 Jun 2016

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

cdorger 6 posts Joined 10/15
07 Jun 2016

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

tiniwings 3 posts Joined 04/16
07 Jun 2016
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.

cdorger 6 posts Joined 10/15
07 Jun 2016

I get it.  Thanks!

dnoeth 4628 posts Joined 11/04
08 Jun 2016

If all columns are NULL you'll get a wrong result, you better add a final NULLIF :)

NULLIF(cast(greatest
( 
cast(COALESCE(date1, date '0001-01-01') AS INT),
cast(COALESCE(date2, date '0001-01-01') AS INT),
cast(COALESCE(date3, date '0001-01-01') AS INT), 
cast(COALESCE(date4, date '0001-01-01') AS INT)
) as date), date '0001-01-01')

 

Dieter

You must sign in to leave a comment.