Convert DB2 date to Teradata date.

Database

Convert DB2 date to Teradata date.

Hi,

I've got a table that is fed from a DB2 table. There is a VARCHAR (9) colunm that has DB2 date formats ('DDMONYYYY' ie.. '18MAR2011'). I need to cast/convert to a date so I can compare to another column defined as a date.

I tried to use below syntax but it only works in the select. It does not work in the Where clause or in a derived table.
CAST(b.beta_acct_open_dt AS DATE FORMAT 'ddmonyyyy') AS Acct_open_dt,

Any help would be appreciated.

Thanks
Dave
3 REPLIES
Senior Apprentice

Re: Convert DB2 date to Teradata date.

Of course this also works in WHERE, but you have to use the correct format:
CAST(b.beta_acct_open_dt AS DATE FORMAT 'ddMMMyyyy')

Dieter

Re: Convert DB2 date to Teradata date.

Here is what I'm trying to solve for. In the below example I tried to use the Date Format and I received invalid date supplied. But is does work in the select statement unless you use this query as a sub-query or derived query.

CREATE VOLATILE TABLE tmp_DB2_dt
( DB2_dt VARCHAR(09) CHARACTER SET UNICODE NOT CASESPECIFIC
) ON COMMIT PRESERVE ROWS;

INSERT INTO tmp_DB2_dt VALUES ('25APR2003');
INSERT INTO tmp_DB2_dt VALUES ('09FEB2000');
INSERT INTO tmp_DB2_dt VALUES ('12SEP2000');
INSERT INTO tmp_DB2_dt VALUES ('01JUN2007');
INSERT INTO tmp_DB2_dt VALUES ('15MAR1989');

/* this select produces an error */
SELECT *
FROM tmp_db2_dt
WHERE CAST(db2_dt AS DATE FORMAT 'ddMMMyyyy') < '2000-01-01'

The query below does work, but I was hoping there was a more elegant way to do it.

SELECT
CAST (CASE WHEN SUBSTR(DB2_dt, 3,3) = 'JAN' THEN SUBSTR(DB2_dt, 6,9) || '/01/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'FEB' THEN SUBSTR(DB2_dt, 6,9) || '/02/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'MAR' THEN SUBSTR(DB2_dt, 6,9) || '/03/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'APR' THEN SUBSTR(DB2_dt, 6,9) || '/04/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'MAY' THEN SUBSTR(DB2_dt, 6,9) || '/05/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'JUN' THEN SUBSTR(DB2_dt, 6,9) || '/06/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'JUL' THEN SUBSTR(DB2_dt, 6,9) || '/07/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'AUG' THEN SUBSTR(DB2_dt, 6,9) || '/08/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'SEP' THEN SUBSTR(DB2_dt, 6,9) || '/09/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'OCT' THEN SUBSTR(DB2_dt, 6,9) || '/10/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'NOV' THEN SUBSTR(DB2_dt, 6,9) || '/11/' || SUBSTR(DB2_dt, 1,2)
WHEN SUBSTR(DB2_dt, 3,3) = 'DEC' THEN SUBSTR(DB2_dt, 6,9) || '/12/' || SUBSTR(DB2_dt, 1,2)
END AS DATE) AS new_db2_dt
FROM tmp_db2_dt
WHERE new_db2_dt < '2000-01-01'
Senior Apprentice

Re: Convert DB2 date to Teradata date.

I'm not shure why your query fails, but when you write the date in the correct way it works as expected:

SELECT *
FROM tmp_db2_dt
WHERE CAST(db2_dt AS DATE FORMAT 'ddMMMyyyy') < date '2000-01-01'

Dieter