CODEWITHSUNDEEP
sasteradata
Dipyaman
Dipyaman

Reputation: 61

Teradata String date to Date type change

I have a Teradata query which I am running using SAS. Once of the Teradata fields I am trying to read has a series of digits which is in string format that basically refers to a date. In the Teradata Field the value is 170919 which mean 2017-09-19. I am unable to convert this value into a valid datetype. Can you please help.

proc sql;
    connect to teradata (schema=&terasilo user=&terauser password=&terapass tdpid=&teradbase);
        create table COL_ASPECT_CALLS_2 as
            select * from connection to teradata(
                select 
                top 10 *
                from &&terasilo..DMI_COL_ASPECT_CALLS 
                where CAST(PROD_DATE_CH AS DATE FORMAT 'yymmdd')='2017-09-19'

                                          );
    disconnect from teradata;       
quit;

Upvotes: 0

Views: 5058

Answers (2)

dnoeth
dnoeth

Reputation: 60482

Instead of dealing with the century break setting or prepending 20 you can simply switch to To_Date('170919', 'yymmdd') which always assumes 21st century for two-digit years.

Upvotes: 3

Tom
Tom

Reputation: 51611

I believe there is a system setting for Teradata that tells it how to convert 2 digit years.

But you could just add the century yourself. Perhaps just pre-pend '20'?

select
  CAST('170901' AS DATE FORMAT 'yymmdd') as NoCentury
, CAST('20'||'170901' AS DATE FORMAT 'yymmdd') as WithCentury

Yields

NoCentury   WithCentury
9/1/1917    9/1/2017

Upvotes: 0

Related Questions