0

I am doing sort of a code migration from Python to Teradata:

The python code is this:

max = min(datetime.today(), date + timedelta(days=90))

where date variable holds a date.

However, in Teradata, I know this min function won't work the same way. And, I have to get the 'date' using a select statement.

SEL min(SELECT CURRENT_TIMESTAMP, SEL MAX(DTM) + INTERVAL '90' DAY FROM BILLS) as max

Those select statements individually run correct. Only thing is I want the minimum of those two dates. Also, the 'SELECT CURRENT_TIMESTAMP' is generating output like 2022-11-16 12:18:37.120000+00:00. I only want 2022-11-16 12:18:37. How can this be done in a single query?

Thank you.

User771
  • 53
  • 5

2 Answers2

1

Were you looking for this one?

SELECT LEAST(13, 6); 
SELECT LEAST( to_char(date1,'YYYYMMDD'), to_char(date2,'YYYYMMDD') ) ...
access_granted
  • 1,807
  • 20
  • 25
  • Thank you. this is giving the output I want in the right format. Howerver, I am worried that now the output is in varchar. I'd like to be it in datetime format. `SELECT (LEAST(to_char(CURRENT_TIMESTAMP(0), 'YYYY-MM-DD HH:MI:SS'), to_char(MAX(DTM) + INTERVAL '90' DAY))) FROM BILLS` – User771 Nov 16 '22 at 18:49
  • Then wrap the character expression in CAST(CAST( ... AS FORMAT 'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP(0))). But it's easier to just have 2 TIMESTAMP(0) arguments for LEAST as in my earlier comment. – Fred Nov 16 '22 at 19:25
0

No reason to convert to VARCHAR. Assuming DTM is TIMESTAMP(0), all you need is:

SELECT LEAST(CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0)),
             MAX(DTM) + INTERVAL '90' DAY)
FROM BILLS;

If DTM has fractional seconds precision but the fractional part is always zero, then you can move the CAST to the outside:

SELECT CAST(LEAST(CURRENT_TIMESTAMP(0),
             MAX(DTM) + INTERVAL '90' DAY) AS TIMESTAMP(0))
FROM BILLS;

Teradata will not directly allow truncation of nonzero fractional seconds unless your system has set TruncRoundReturnTimestamp to TRUE, so if DTM potentially has fractional seconds then you may be stuck with a somewhat clumsy workaround like converting to character and back or subtracting the fractional seconds some other way such as

DTM - (EXTRACT(SECOND FROM DTM) MOD 1)*INTERVAL '1' SECOND

before you can CAST to TIMESTAMP(0)

Fred
  • 1,916
  • 1
  • 8
  • 16
  • DTM is a timestamp, but in the format like yyyy-mm-dd hh:mm:ss. I find that the timestamp is either creating the values with milliseconds or timezone. You handle the milliseconds and the time zone sticks and vice versa. – User771 Nov 21 '22 at 14:36
  • CURRENT_TIMESTAMP is a TIMESTAMP(6) WITH TIME ZONE. The external FORMAT is not directly tied to the internal representation / precision. I have edited my answer to cover some additional cases. – Fred Nov 21 '22 at 15:55