CODEWITHSUNDEEP
sqldateteradata
user3901666
user3901666

Reputation: 409

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'
      );

Upvotes: 1

Views: 10092

Answers (1)

dnoeth
dnoeth

Reputation: 60482

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.

Upvotes: 1

Related Questions