teradata-numeric to date (query)

risky_analyst
risky_analyst used Ask the Experts™
on
Hi I am using SAS with a teradata database.  In my SQL statement, I am trying to write a WHERE statement querying only for certain date ranges.  The date variable in the teradata datebase is numeric and is 6 digits.  For example 82311 is August 23, 2011.  Can someone help me with the syntax?  Thanks

where CAST ( (CAST (run_date AS date FORMAT 'MMDDYY') ) AS CHAR(6)) >= 080111);

ERROR: Teradata row not delivered (trget): Invalid date supplied for
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi

How have you framed your SQl query.  Is it a pss-thru query:

PROC SQL;
  Connect to terabase(connection details);
  select * from connection to terabase(
      SQL query
);
quit;

or are you using libname access?

If you are using libname access, SAS should do the conversion for you, without the need for a CAST statement

Author

Commented:
This was done by pass through

Author

Commented:
Anyone?
Commented:
use math to rearrange:
WHERE  (run_date - (run_date/100)*100)*10000 + run_date/100 (INTEGER) >=110801

Notice the reordering of the value so year is first, then month, then day.
In TD, the Greater than a date does not work, when put Month, Date, Year in order. It should be Year, Month, Date

This WHERE clause below tries to re-order the Integer stored in Teradata into meaningful Date format and applies >= comparison.

WHERE CAST(SUBSTR(CAST(CAST(82311 AS FORMAT'9(6)') AS CHAR(6)),5,2)||SUBSTR(CAST(CAST(82311 AS FORMAT'9(6)') AS CHAR(6)),1,2)||SUBSTR(CAST(CAST(82311 AS FORMAT'9(6)') AS CHAR(6)),3,2) AS INTEGER)

>=110823

which is 2011, Aug, 23

HTH

Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial