Invalid operation for DateTime or Interval - When inserting using a stored procedure

Database
Teradata Employee

Invalid operation for DateTime or Interval - When inserting using a stored procedure

Getting this error when inserting as follows 

 

  INSERT INTO EAS41DMO_STG.STG_TIME_DIMENSION

    (

      FISCAL_CALENDAR_ID,

      CALENDAR_DATE,

      FISCAL_CALENDAR_NAME,

      DAY_OF_CAL_YEAR,

      DAY_OF_CAL_QUARTER,

      DAY_OF_CAL_MONTH,

      DAY_OF_CAL_WEEK,

      YEAR_NUMBER,

      YEAR_START_DATE,

      YEAR_END_DATE,

      QUARTER_NUMBER,

      QUARTER_START_DATE,

      QUARTER_END_DATE,

      QUARTERS_IN_YEAR,

      MONTH_NUMBER,

      NAME_OF_MONTH,

      SHORT_NAME_OF_MONTH,

      MONTH_START_DATE,

      MONTH_END_DATE,

      MONTHS_IN_QUARTER,

      MONTHS_IN_YEAR,

      ISO_WEEK_NUMBER,

      ISO_WEEK_YEAR_NUMBER,

      WEEK_START_DATE,

      WEEK_END_DATE,

      WEEKS_IN_YEAR,

      DAY_NUMBER,

      NAME_OF_DAY,

      SHORT_NAME_OF_DAY,

      DAYS_IN_YEAR,

      DAYS_IN_QUARTER,

      DAYS_IN_MONTH,

      DAYS_IN_WEEK_YEAR,

      WORKING_DAY_FLAG,

      FISCAL_YEAR,

      FISCAL_YEAR_START_DATE,

      FISCAL_YEAR_END_DATE,

      DAYS_IN_FISCAL_YEAR,

      FISCAL_PERIOD_CODE,

      FISCAL_PERIOD,

      PERIOD_START_DATE,

      PERIOD_END_DATE,

      DAYS_IN_PERIOD,

      PERIODS_IN_FISCAL_YEAR,

      SPECIAL_PERIODS_IN_FYEAR,

      TAS_CREATE_DATE,

      TAS_CHANGE_DATE,

      TAS_EXTRACTION_DATE,

      DWH_DELETION_FLAG

    )

  SELECT

      V1.FISCAL_CALENDAR_ID,

      V1.CALENDAR_DATE,

      V1.FISCAL_CALENDAR_NAME,

      V1.DAY_OF_CAL_YEAR,

      V1.DAY_OF_CAL_QUARTER,

      V1.DAY_OF_CAL_MONTH,

      V1.DAY_OF_CAL_WEEK,

      V1.YEAR_NUMBER,

      V1.YEAR_START_DATE,

      V1.YEAR_END_DATE,

      V1.QUARTER_NUMBER,

      V1.QUARTER_START_DATE,

      V1.QUARTER_END_DATE,

      V1.QUARTERS_IN_YEAR,

      V1.MONTH_NUMBER,

      V1.NAME_OF_MONTH,

      V1.SHORT_NAME_OF_MONTH,

      V1.MONTH_START_DATE,

      V1.MONTH_END_DATE,

      V1.MONTHS_IN_QUARTER,

      V1.MONTHS_IN_YEAR,

      V1.ISO_WEEK_NUMBER,

      V1.ISO_WEEK_YEAR_NUMBER,

      V1.WEEK_START_DATE,

      V1.WEEK_END_DATE,

      V1.WEEKS_IN_YEAR,

      V1.DAY_NUMBER,

      V1.NAME_OF_DAY,

      V1.SHORT_NAME_OF_DAY,

      V1.DAYS_IN_YEAR,

      V1.DAYS_IN_QUARTER,

      V1.DAYS_IN_MONTH,

      V1.DAYS_IN_WEEK_YEAR,

      V1.WORKING_DAY_FLAG,

      V1.FISCAL_YEAR,

      V1.FISCAL_YEAR_START_DATE,

      V1.FISCAL_YEAR_END_DATE,

      V1.DAYS_IN_FISCAL_YEAR,

      V1.FISCAL_PERIOD_CODE,

      V1.FISCAL_PERIOD,

      V1.PERIOD_START_DATE,

      V1.PERIOD_END_DATE,

      V1.DAYS_IN_PERIOD,

      V1.PERIODS_IN_FISCAL_YEAR,

      V1.SPECIAL_PERIODS_IN_FYEAR,

      V1.TAS_CREATE_DATE,

      V1.TAS_CHANGE_DATE,

      V1.TAS_EXTRACTION_DATE,

      V1.DWH_DELETION_FLAG

  FROM EAS41DMO_STG.VW_STG_TIME_DIMENSION V1

;


Accepted Solutions
Highlighted
Teradata Employee

Re: Invalid operation for DateTime or Interval - When inserting using a stored procedure

OK Guys. Found the Solution. Replaced the TO_DATE function to CAST and also used a TRIM for the YEAR_NUMBER.

 

T1.CALENDAR_DATE -  to_date(('0101' || T1.YEAR_NUMBER), 'DDMMYYYY') + 1

 

So the above statement becomes

 

T1.CALENDAR_DATE -  CAST(('0101' || TRIM(T1.YEAR_NUMBER)) AS DATE FORMAT 'DDMMYYYY') + 1

 

It works!!

 

I opened a ticket with TAS Support. I need to inform them.

1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

Re: Invalid operation for DateTime or Interval - When inserting using a stored procedure

problem seems to be this view 

 

REPLACE VIEW TAS4X_STG.VW_STG_TIME_DIMENSION (

  FISCAL_CALENDAR_ID,

  CALENDAR_DATE,

  FISCAL_CALENDAR_NAME,

  DAY_OF_CAL_YEAR,

  DAY_OF_CAL_QUARTER,

  DAY_OF_CAL_MONTH,

  DAY_OF_CAL_WEEK,

  YEAR_NUMBER,

  YEAR_START_DATE,

  YEAR_END_DATE,

  QUARTER_NUMBER,

  QUARTER_START_DATE,

  QUARTER_END_DATE,

  QUARTERS_IN_YEAR,

  MONTH_NUMBER,

  NAME_OF_MONTH,

  SHORT_NAME_OF_MONTH,

  MONTH_START_DATE,

  MONTH_END_DATE,

  MONTHS_IN_QUARTER,

  MONTHS_IN_YEAR,

  ISO_WEEK_NUMBER,

  ISO_WEEK_YEAR_NUMBER,

  WEEK_START_DATE,

  WEEK_END_DATE,

  WEEKS_IN_YEAR,

  DAY_NUMBER,

  NAME_OF_DAY,

  SHORT_NAME_OF_DAY,

  DAYS_IN_YEAR,

  DAYS_IN_QUARTER,

  DAYS_IN_MONTH,

  DAYS_IN_WEEK_YEAR,

  WORKING_DAY_FLAG,

  FISCAL_YEAR,

  FISCAL_YEAR_START_DATE,

  FISCAL_YEAR_END_DATE,

  DAYS_IN_FISCAL_YEAR,

  FISCAL_PERIOD_CODE,

  FISCAL_PERIOD,

  PERIOD_START_DATE,

  PERIOD_END_DATE,

  DAYS_IN_PERIOD,

  PERIODS_IN_FISCAL_YEAR,

  SPECIAL_PERIODS_IN_FYEAR,

  TAS_CREATE_DATE,

  TAS_CHANGE_DATE,

  TAS_EXTRACTION_DATE,

  DWH_DELETION_FLAG)

AS

LOCKING ROW FOR ACCESS

SELECT

  T1.FISCAL_CALENDAR_ID,

  T1.CALENDAR_DATE,

  T1.FISCAL_CALENDAR_NAME,

  T1.DAY_OF_CAL_YEAR,

  CASE

    WHEN T1.MONTH_NUMBER = 1 OR T1.MONTH_NUMBER = 2 OR T1.MONTH_NUMBER = 3

      THEN T1.CALENDAR_DATE -  to_date(('0101' || T1.YEAR_NUMBER), 'DDMMYYYY') + 1

    WHEN T1.MONTH_NUMBER = 4 OR T1.MONTH_NUMBER = 5 OR T1.MONTH_NUMBER = 6

      THEN T1.CALENDAR_DATE -  to_date(('0104' || T1.YEAR_NUMBER), 'DDMMYYYY') + 1

    WHEN T1.MONTH_NUMBER = 7 OR T1.MONTH_NUMBER = 8 OR T1.MONTH_NUMBER = 9

      THEN T1.CALENDAR_DATE -  to_date(('0107' || T1.YEAR_NUMBER), 'DDMMYYYY') + 1

    WHEN T1.MONTH_NUMBER = 10 OR T1.MONTH_NUMBER = 11 OR T1.MONTH_NUMBER = 12

      THEN T1.CALENDAR_DATE -  to_date(('0110' || T1.YEAR_NUMBER), 'DDMMYYYY') + 1

    ELSE 0

  END AS DAY_OF_CAL_QUARTER,

  T1.DAY_OF_CAL_MONTH,

  T1.DAY_OF_CAL_WEEK,

  T1.YEAR_NUMBER,

  Q4.YEAR_START_DATE,

  Q4.YEAR_END_DATE,

  T1.QUARTER_NUMBER,

  Q3.QUARTER_START_DATE,

  Q3.QUARTER_END_DATE,

  4 AS QUARTERS_IN_YEAR,

  T1.MONTH_NUMBER,

  T1.NAME_OF_MONTH,

  T1.SHORT_NAME_OF_MONTH,

  Q2.MONTH_START_DATE,

  Q2.MONTH_END_DATE,

  3 AS MONTHS_IN_QUARTER,

  12 AS MONTHS_IN_YEAR,

  T1.ISO_WEEK_NUMBER,

  T1.ISO_WEEK_YEAR_NUMBER,

  Q1.WEEK_START_DATE,

  Q1.WEEK_END_DATE,

  Q4.WEEKS_IN_YEAR,

  T1.DAY_OF_CAL_MONTH AS DAY_NUMBER,

  T1.NAME_OF_DAY,

  T1.SHORT_NAME_OF_DAY,

  Q4.DAYS_IN_YEAR,

  Q3.DAYS_IN_QUARTER,

  Q2.DAYS_IN_MONTH,

  Q1.DAYS_IN_WEEK_YEAR,

  T1.WORKING_DAY_FLAG,

  T1.FISCAL_YEAR,

  Q5.FISCAL_YEAR_START_DATE,

  Q5.FISCAL_YEAR_END_DATE,

  Q5.DAYS_IN_FISCAL_YEAR,

  'FP' || T1.FISCAL_YEAR || T1.FISCAL_PERIOD AS FISCAL_PERIOD_CODE,

  T1.FISCAL_PERIOD,

  T1.PERIOD_BEGIN_DATE AS PERIOD_START_DATE,

  T1.PERIOD_END_DATE,

  CAST(T1.DAYS_IN_PERIOD AS INTEGER),

  CAST(T1.NUMBER_OF_PERIODS AS INTEGER) AS PERIODS_IN_FISCAL_YEAR,

  CAST(T1.NUMBER_OF_SPECIAL_PERIODS AS INTEGER) AS SPECIAL_PERIODS_IN_FYEAR,

  T1.TAS_CREATE_DATE,

  T1.TAS_CHANGE_DATE,

  T1.TAS_EXTRACTION_DATE,

  T1.DWH_DELETION_FLAG

FROM TAS4X_STG.VW_STG_TMP_WH_CALENDAR T1

INNER JOIN (SELECT

               T2.FISCAL_CALENDAR_ID,

               T2.ISO_WEEK_YEAR_NUMBER,

               T2.ISO_WEEK_NUMBER,

               count(T2.CALENDAR_DATE) AS DAYS_IN_WEEK_YEAR,

               min(T2.CALENDAR_DATE) AS WEEK_START_DATE,

               max(T2.CALENDAR_DATE) AS WEEK_END_DATE

             FROM TAS4X_STG.VW_STG_TMP_WH_CALENDAR T2

             GROUP BY

               T2.FISCAL_CALENDAR_ID,

               T2.ISO_WEEK_YEAR_NUMBER,

               T2.ISO_WEEK_NUMBER

            ) Q1

  ON Q1.FISCAL_CALENDAR_ID = T1.FISCAL_CALENDAR_ID

  AND Q1.ISO_WEEK_YEAR_NUMBER = T1.ISO_WEEK_YEAR_NUMBER

  AND Q1.ISO_WEEK_NUMBER = T1.ISO_WEEK_NUMBER            

INNER JOIN (SELECT

               T3.FISCAL_CALENDAR_ID,

               T3.YEAR_NUMBER,

               T3.MONTH_NUMBER,

               count(T3.CALENDAR_DATE) AS DAYS_IN_MONTH,

               min(T3.CALENDAR_DATE) AS MONTH_START_DATE,

               max(T3.CALENDAR_DATE) AS MONTH_END_DATE

             FROM TAS4X_STG.VW_STG_TMP_WH_CALENDAR T3

             GROUP BY

               T3.FISCAL_CALENDAR_ID,

               T3.YEAR_NUMBER,

               T3.MONTH_NUMBER

            ) Q2

  ON Q2.FISCAL_CALENDAR_ID = T1.FISCAL_CALENDAR_ID

  AND Q2.YEAR_NUMBER = T1.YEAR_NUMBER

  AND Q2.MONTH_NUMBER = T1.MONTH_NUMBER

INNER JOIN (SELECT

               T4.FISCAL_CALENDAR_ID,

               T4.YEAR_NUMBER,

               T4.QUARTER_NUMBER,

               count(T4.CALENDAR_DATE) AS DAYS_IN_QUARTER,

               min(T4.CALENDAR_DATE) AS QUARTER_START_DATE,

               max(T4.CALENDAR_DATE) AS QUARTER_END_DATE

             FROM TAS4X_STG.VW_STG_TMP_WH_CALENDAR T4

             GROUP BY

               T4.FISCAL_CALENDAR_ID,

               T4.YEAR_NUMBER,

               T4.QUARTER_NUMBER

            ) Q3

  ON Q3.FISCAL_CALENDAR_ID = T1.FISCAL_CALENDAR_ID

  AND Q3.YEAR_NUMBER = T1.YEAR_NUMBER

  AND Q3.QUARTER_NUMBER = T1.QUARTER_NUMBER

INNER JOIN (SELECT

               T5.FISCAL_CALENDAR_ID,

               T5.YEAR_NUMBER,

               count(T5.CALENDAR_DATE) AS DAYS_IN_YEAR,

               max(T5.ISO_WEEK_NUMBER) AS WEEKS_IN_YEAR,

               min(T5.CALENDAR_DATE) AS YEAR_START_DATE,

               max(T5.CALENDAR_DATE) AS YEAR_END_DATE

             FROM TAS4X_STG.VW_STG_TMP_WH_CALENDAR T5

             GROUP BY

               T5.FISCAL_CALENDAR_ID,

               T5.YEAR_NUMBER

            ) Q4

  ON Q4.FISCAL_CALENDAR_ID = T1.FISCAL_CALENDAR_ID

  AND Q4.YEAR_NUMBER = T1.YEAR_NUMBER

INNER JOIN (SELECT

               T6.FISCAL_CALENDAR_ID,

               T6.FISCAL_YEAR,

               count(T6.CALENDAR_DATE) AS DAYS_IN_FISCAL_YEAR,

               min(T6.CALENDAR_DATE) AS FISCAL_YEAR_START_DATE,

               max(T6.CALENDAR_DATE) AS FISCAL_YEAR_END_DATE

             FROM TAS4X_STG.VW_STG_TMP_WH_CALENDAR T6

             GROUP BY

               T6.FISCAL_CALENDAR_ID,

               T6.FISCAL_YEAR

            ) Q5

  ON Q5.FISCAL_CALENDAR_ID = T1.FISCAL_CALENDAR_ID

  AND Q5.FISCAL_YEAR = T1.FISCAL_YEAR;

Teradata Employee

Re: Invalid operation for DateTime or Interval - When inserting using a stored procedure

I have further narrowed down the problem. The problem is with this CASE statment

 

CASE

    WHEN T1.MONTH_NUMBER = 1 OR T1.MONTH_NUMBER = 2 OR T1.MONTH_NUMBER = 3

      THEN T1.CALENDAR_DATE -  to_date(('0101' || T1.YEAR_NUMBER), 'DDMMYYYY') + 1

    WHEN T1.MONTH_NUMBER = 4 OR T1.MONTH_NUMBER = 5 OR T1.MONTH_NUMBER = 6

      THEN T1.CALENDAR_DATE -  to_date(('0104' || T1.YEAR_NUMBER), 'DDMMYYYY') + 1

    WHEN T1.MONTH_NUMBER = 7 OR T1.MONTH_NUMBER = 8 OR T1.MONTH_NUMBER = 9

      THEN T1.CALENDAR_DATE -  to_date(('0107' || T1.YEAR_NUMBER), 'DDMMYYYY') + 1

    WHEN T1.MONTH_NUMBER = 10 OR T1.MONTH_NUMBER = 11 OR T1.MONTH_NUMBER = 12

      THEN T1.CALENDAR_DATE -  to_date(('0110' || T1.YEAR_NUMBER), 'DDMMYYYY') + 1

    ELSE 0

  END AS DAY_OF_CAL_QUARTER,

 

This is causing an Invalid DateTime or Interval Error

Junior Contributor

Re: Invalid operation for DateTime or Interval - When inserting using a stored procedure

The automatic typecast of T1.YEAR_NUMBER results in leading spaces, use TRIM(T1.YEAR_NUMBER) instead.

And all those self-joins to the calendar view can be replaced by MIN/MAX/COUNT OVER

 

But hopefully this is a one time Insert/Select to create a permanent calendar table.

Teradata Employee

Re: Invalid operation for DateTime or Interval - When inserting using a stored procedure

Thanks for the response. I tried using that. But the problem persists. 

Highlighted
Teradata Employee

Re: Invalid operation for DateTime or Interval - When inserting using a stored procedure

OK Guys. Found the Solution. Replaced the TO_DATE function to CAST and also used a TRIM for the YEAR_NUMBER.

 

T1.CALENDAR_DATE -  to_date(('0101' || T1.YEAR_NUMBER), 'DDMMYYYY') + 1

 

So the above statement becomes

 

T1.CALENDAR_DATE -  CAST(('0101' || TRIM(T1.YEAR_NUMBER)) AS DATE FORMAT 'DDMMYYYY') + 1

 

It works!!

 

I opened a ticket with TAS Support. I need to inform them.

Junior Contributor

Re: Invalid operation for DateTime or Interval - When inserting using a stored procedure

I doubt it's the TO_DATE, but why don't you replace all those calculations with TRUNC(T1.CALENDAR_DATE, 'Q')?