Date in varchar and DD-MMM-YY FORMAT

Database
Enthusiast

Date in varchar and DD-MMM-YY FORMAT

I am stuck in this query. The below part "TRANSMITTED_DATE LIKE '17-JUL-14'" is hardcoded. I want to make it a generalized one.

I want to do something like this

WHERE CAST(TRANSMITTED_DATE  AS DATE FORMAT 'DD-MMM-YY') BETWEEN 
CAST
(CURRENT_DATE AS DATE FORMAT 'DD-MMM-YY')
AND CAST(CURRENT_DATE AS DATE FORMAT 'DD-MMM-YY')-7

i.e the last seven days record from the last time this query is run against the table.

But, the transmitted date is VARCHAR field in the table and format is like 31-OCT-13 i.e DD-MMM-YY.

Please help me with the query.

SELECT 
MLI_MDL_NUMBER
as DOCUMENTUM_MLI
,TRANSMITTAL_NUMBER
,PROJECT_ID
,TRANSMITTED_DATE
FROM GEEDW_PLP_BULK_V.CDR_DOCUMENTUM_TRSMTL
WHERE
( TRANSMITTED_DATE LIKE '17-JUL-14' OR
TRANSMITTED_DATE
LIKE '18-JUL-14' OR
TRANSMITTED_DATE
LIKE '19-JUL-14' OR
TRANSMITTED_DATE
LIKE '20-JUL-14' OR
TRANSMITTED_DATE
LIKE '21-JUL-14' OR
TRANSMITTED_DATE
LIKE '22-JUL-14' OR
TRANSMITTED_DATE
LIKE '23-JUL-14' OR
TRANSMITTED_DATE
LIKE '24-JUL-14' OR
TRANSMITTED_DATE
LIKE '25-JUL-14'
);

2 REPLIES
Senior Apprentice

Re: Date in varchar and DD-MMM-YY FORMAT

Depending on a global system setting (Century Break in dbscontrol) or your Teradata release this might be easy.

If you're on TD14 you can utilize Oracle's TO_DATE:

WHERE TO_DATE(TRANSMITTED_DATE, 'dd-mon-yy')
BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE

Otherwise try following query and check what's returned:

SELECT CAST('17-JUL-14' AS DATE FORMAT 'DD-MMM-YY')

If it's 2014-07-17 you can simply use

WHERE CAST(TRANSMITTED_DATE AS DATE FORMAT 'DD-MMM-YY') 
BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE

If it's 1914-07-17

WHERE CAST(SUBSTRING(TRANSMITTED_DATE FROM 1 FOR 7) || '20' ||
SUBSTRING(TRANSMITTED_DATE FROM 8) AS DATE FORMAT 'DD-MMM-YY')
BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE

Of course all go them are bad because every row needs to be converted from string to date, which is a big overhead and you'll loose an existing statistics.

If this is a big table and you need to run that query often you should try to change the datatype to a DATE and do the typecast once during import.

And there's another problem: if there's any string representing a non-valid date your query will fail.

Enthusiast

Re: Date in varchar and DD-MMM-YY FORMAT

Thanks Dieter,

That worked!!   You make our life easier :)..

--Amit