Date in varchar and dd-mmm-yy format

Question

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

You now have "between upperdate and lowerdate", would it help to switch those limits? Also, what problems do you see? (error messages, failed data, ...) — Hans Keﬆing, Sep 05 '14 at 08:31

score 1 · Accepted Answer · answered Sep 05 '14 at 10:40

1

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.

answered Sep 05 '14 at 10:40

dnoeth

59,503
4
39
56

Hi Dnoeth, Thank you for response. I tried to add another interval of 100 year CAST(TRANSMITTED_DATE AS DATE FORMAT 'DD-MMM-YY') + INTERVAL '100' YEAR BETWEEN CAST(CURRENT_DATE AS DATE FORMAT 'DD-MMM-YY')-7 AND CAST(CURRENT_DATE AS DATE FORMAT 'DD-MMM-YY').....This was working..will this cause any issues? – user3901666 Sep 05 '14 at 11:02
You don't need to apply a cast to CURRENT_DATE and this query will fail if there's any row with 29-feb-00 because 1900 was no leap year. – dnoeth Sep 05 '14 at 11:05
Hi Dnoeth, Thank you for your response.I didn't get when you said "this query will fail if there's any row with 29-feb-00 because 1900 was no leap year". Could you please explain a little bit. – user3901666 Sep 05 '14 at 19:34
When you specify '29-feb-00' Teradata will try to cast it to 1900-02-29. Of course this date doesn't exist and the cast will fail with an "invalid date" error. – dnoeth Sep 05 '14 at 20:22
Got it :) thanks a lot..will go with your approach ...thank you. – user3901666 Sep 07 '14 at 17:34

Date in varchar and dd-mmm-yy format

1 Answers1