TD - How to convert different date formats to YYYYMMDD

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

TD - How to convert different date formats to YYYYMMDD

Hi Folks,

 

I have loaded the data from Mainframe file into Teradata Staging table as an integer field. Now challenge is to load the date fields in YYYYMMDD format. Please find below few scenarios. Appreciate your help. Thanks

 

For all the below scenarios target table should have it in  'YYYYMMDD' format.

 

Scenario 1 :

For ex:  111 (MDD) --> Actually data would be in this format in file : 000111 i.e. 2000/01/11 (11- January-2000).

While loading into table only 111 would retain as all the zero's would be truncated. Same is applicable for below scenarios.

 

Scenario 2:

For ex: 80111 (YMMDD) --> 080111 --> 2008/01/11

 

Scenario 3:

For ex: 991213 (YYMMDD) --> 1999/12/13

 

My understanding is to put something to make sure if century field is greater than 50 then it would append with '19' (CC) otherwise it would be '20'. Please advise. Looking forward for some exciting soutions.

 


Accepted Solutions
Supporter

Re: TD - How to convert different date formats to YYYYMMDD

Hi anuj782,

 

One way to do the conversion:

/* Creating your date field table */
CREATE MULTISET VOLATILE TABLE STAGE_TEST
(
  TEST_DATE INTEGER
)
ON COMMIT PRESERVE ROWS;

INSERT INTO STAGE_TEST VALUES (111);
INSERT INTO STAGE_TEST VALUES (80111);
INSERT INTO STAGE_TEST VALUES (991213);

/* Your required query */
SELECT
  TEST_DATE
, CASE
    WHEN TEST_DATE > 501231
    THEN CAST(TO_CHAR(19000000+TEST_DATE) AS DATE FORMAT 'YYYYMMDD') 
    ELSE CAST(TO_CHAR(20000000+TEST_DATE) AS DATE FORMAT 'YYYYMMDD') 
  END
FROM
  STAGE_TEST;

Hope this helps.

 

EDITED: CORRECTED SOLUTION 

 

Thanks,

Rohan Sawant

1 ACCEPTED SOLUTION
4 REPLIES
Supporter

Re: TD - How to convert different date formats to YYYYMMDD

Hi anuj782,

 

One way to do the conversion:

/* Creating your date field table */
CREATE MULTISET VOLATILE TABLE STAGE_TEST
(
  TEST_DATE INTEGER
)
ON COMMIT PRESERVE ROWS;

INSERT INTO STAGE_TEST VALUES (111);
INSERT INTO STAGE_TEST VALUES (80111);
INSERT INTO STAGE_TEST VALUES (991213);

/* Your required query */
SELECT
  TEST_DATE
, CASE
    WHEN TEST_DATE > 501231
    THEN CAST(TO_CHAR(19000000+TEST_DATE) AS DATE FORMAT 'YYYYMMDD') 
    ELSE CAST(TO_CHAR(20000000+TEST_DATE) AS DATE FORMAT 'YYYYMMDD') 
  END
FROM
  STAGE_TEST;

Hope this helps.

 

EDITED: CORRECTED SOLUTION 

 

Thanks,

Rohan Sawant

Re: TD - How to convert different date formats to YYYYMMDD

Thank you so much Rohan for quick response. Appreciate it.

SELECT
  TEST_DATE
, CASE
    WHEN TEST_DATE > 501231
    THEN CAST(TO_CHAR(19000000+TEST_DATE) AS DATE FORMAT 'YYYYMMDD') 
    ELSE CAST(TO_CHAR(20000000+TEST_DATE) AS DATE FORMAT 'YYYYMMDD') 
  END
FROM
  STAGE_TEST;

Made minor typo changes, added one more zero in else statement and replace '1' with '2'.  Once again. Thank you :)

 

Enthusiast

Re: TD - How to convert different date formats to YYYYMMDD

SyntaxEditor Code Snippet

Hope this will help. Replace 991213 with your different value like 000111,080111 to test

select
case when to_date('991213','YYMMDD') > Date then ADD_MONTHS(to_date('991213','YYMMDD'),-1200) else to_date('991213','YYMMDD') end
Junior Contributor

Re: TD - How to convert different date formats to YYYYMMDD

This can be simplified as there's no need for a double cast.

The integer date is actually quite similar to Teradata's internal date format (YYYY-1900)*10000 + MM*100 + DD, just the 19 vs. 20 needs special treatment:

, CASE
    WHEN TEST_DATE > 501231
    THEN Cast(TEST_DATE AS DATE) 
    ELSE Cast(1000000+TEST_DATE AS DATE) 
  END