Is TRUNC(Date) broken for ISO 8601 week-based calendar dates?

Database
Highlighted
Enthusiast

Is TRUNC(Date) broken for ISO 8601 week-based calendar dates?

The docs at https://docs.teradata.com/reader/756LNiPSFdY~4JcCCcR5Cw/o0O1191nmEeZQaW1BKk4QQ for 16.20 say

TRUNC(Date)
Purpose
Truncates date_value based on the format specified by fmt.
....
IYYY ISO year
....
Example: Returning a Date Value for the Beginning of the Month
The following queries:

SELECT TRUNC(CAST('2003/09/15' AS DATE), 'MONTH') (FORMAT 'yyyy-mm-dd');
....

SELECT TRUNC(CAST('2003/09/17' AS DATE), 'RM') (FORMAT 'yyyy-mm-dd');
all return the result 2003-09-01. The date was truncated to the beginning of the month.


So, I expect that TRUNC(DATE XXXX, 'IYYY') will return the date that is the first day of the ISO 8601 week-based calendar year containing XXXX. I.e. truncate to the start of the ISO year.

 

TRUNC(DATE '2014-12-29', 'IYYY') fails to meet that expectation.
As it happens, 29Dec2014 *is* the first day of ISO year 2015. So the call should return the parameter.

It doesn't.
SELECT TRUNC(DATE '2014-12-29', 'IYYY')
2013-12-30

 

30Dec2013 is the first day of ISO year 2014. So the operation appears to be along the lines of
"OK, we have 29Dec2014, which is ISO year 2014" (which is incorrect)
"What's the first day of ISO year 2014?"
"There's your answer: 30Dec2013."

 

FWIW, the exact same expression in Oracle or Vertica returns the expected 29Dec2014 date.

3 REPLIES 3
Ambassador

Re: Is TRUNC(Date) broken for ISO 8601 week-based calendar dates?

It's a bug (I noticed it the other day when I played around with your add ISO-year/quarter), please open an incident with Teradata support.

The first days of a year belonging to ISO-week 52/53 and the last days of a year belonging to ISO-week 1 return the wrong value.

 

As workaround you can use Td_Year_Begin(calendar_date, 'iso') which returns the correct value (but is restricted to the years 1900 to 2100).
Btw, there are similar functions for the begin of the week/quarter.

Enthusiast

Re: Is TRUNC(Date) broken for ISO 8601 week-based calendar dates?

Apparently I don't have access to the support system. Following the links to create an incident leads to a spinning-forever section that looks like it would list recent incidents if it worked; the URL of that page ends with userNotFoundException%3Dtrue.

 

Is this something I can fix from here?

Ambassador

Re: Is TRUNC(Date) broken for ISO 8601 week-based calendar dates?

Talk to your DBA to open an incident :-)