Teradata String date to Date type change

0
votes

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;
2
sasteradata
Why don't you use PROD_DATE_CH ='170919' instead of CASTing the string to a date` - dnoeth
I cannot use it as I need to give a date range Eg: 170919 to 171231. String value will not be easy to increment just like date columns. - Dipyaman
@Dipyaman Character string in YYMMDD format can be tested using inequalities, unlike strings in MDY or DMY order. You could use format in SAS to generate character strings from date values. For example to create a macro variable that you could use in your Teradata query: %let nextmnth=%bquote('%sysfunc(intnx(month,&dateliteral,1,b),yymmddn6)');. Once you have the string on the SAS side you can use INPUT function to convert it to a real date. - Tom

2 Answers

3
votes

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.

0
votes

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