CODEWITHSUNDEEP
sqlaggregate-functionsteradata
Grigory P
Grigory P

Reputation: 191

Teradata: how to get greatest DATE in a row?

I'm trying to get maximum date in a row. Both fuctions, MAX and Greatest return errors:

SEL Max(date1,date2,date3...)
SELECT Failed. 3706:  Syntax error: expected something between a string or a Unicode character literal and ','. 

SEL Greatest(date1,date2,date3...)
SELECT Failed. 9881:  Function 'GREATEST' called with an invalid number or type of parameters

How to solve this? thx

Upvotes: 2

Views: 5981

Answers (4)

dnoeth
dnoeth

Reputation: 60513

Yep, it's stupid, LEAST and GREATEST don't work with date/time (fixed in 16.10).

As a workaround you can cast it to integer:

SEL cast(GREATEST(cast(date1 as int)
                 ,cast(date2 as int)
                 ,cast(date3 as int)
                 ...) as date)

Hopefully there's no NULL, otherwise it gets ugly with additional COALESCEs/NULLIF

Upvotes: 2

random_visitor
random_visitor

Reputation: 21

In TD 16.x0, GREATEST/LEAST work both with dates and timestamps. However users may need to add database, like it was UDF function:

SELECT TD_SYSFNLIB.LEAST(CURRENT_TIMESTAMP(0),ADD_MONTHS(CURRENT_TIMESTAMP(0),2))

Upvotes: 0

Venkat Singri
Venkat Singri

Reputation: 104

Try this :

SELECT (
        SELECT MAX(maxdate)
        FROM (
            VALUES (date1)
                ,(date2)
                ,(date3)
            ) AS maximumdate(maxdate)
        ) AS maxdate
FROM #temp

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143103

According to documentation, arguments for the GREATEST function can't be dates. Try to convert them to strings in the YYYYMMDD (or similar) format (so that the result wouldn't suffer from issues when strings are being sorted).

Upvotes: 1

Related Questions