Need help!! Extracting year from table Name

Database

Need help!! Extracting year from table Name

I need to write code to delete some tables that are previous 36 months from production as a part of clean up.The tables are stored as tablename_yymm.

I have written it as

bteq << EOF

.logon dbname/dwtst01,pwd;

.SET ECHOREQ ON

.SET RETRY ON

.SET TIMEMSG DEFAULT

.SET ERRORLEVEL 3807 SEVERITY 0;

.run file logon.txt

.set width 200

.set titledashes off

.set format off

.SET RTITLE' '

.export report file=del_app_work.run

SELECT DISTINCT 'DROP TABLE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';' (Title '')

FROM DBC.TABLES WHERE DATABASENAME='dbname'AND TABLEKIND = 'T'AND TABLENAME  IN

(tablename_1203,tablename_1204)    

AND CAST(CreateTimeStamp AS DATE) <  add_months(date - extract(day from date) + 1, -35);

.Export report file=del_app_work.sql

.run file = del_app_work.run

.logoff

.exit

EOF

I have identified the tables to be dropped based on createtimestamp but my lead says that it should be cast from table name itself ie extract the month and year from tablename

Unable to do it.

Can anyone here plz help me it is really appreciated.

Thanks in advance!!




thanks, AP

2 REPLIES
Teradata Employee

Re: Need help!! Extracting year from table Name

Assuming the two-digit years are all 20yy:

CAST('20'||SUBSTRING(TABLENAME FROM CHARACTER_LENGTH(TABLENAME) - 3 FOR 4)||'01' AS DATE FORMAT 'YYYYMMDD')

Note: Use DBC.TablesV. If you use backward compatibility view DBC.Tables, you will need CHARACTER_LENGTH(TRIM(TABLENAME)).

DS
Enthusiast

Re: Need help!! Extracting year from table Name

sel regexp_replace ('tablename_1203','([a-z]+[_])','');