I have a column with a date and I need to show it like mm/yyyy or yyyy/mm can someone help me do this?
select date (format 'mm/yyyy');select date (format 'mm/yyyy'); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. Date-------12/2008
Or more generally:specify the formatting to be used for conversions to/from external character form, then tell Teradata to do the conversion.SELECT CAST(CAST(dateCol AS FORMAT 'MM/YYYY') AS CHAR(7))The other example works becausedateCol (FORMAT 'MM/YYYY') is old Teradata syntax for the inner CAST andusing BTEQ in field mode to generate a report effectively causes implicit CAST to [VAR]CHAR
Am I running into the same problem. When I run SELECT DDOBJ(format 'YYYY-MM-DD') from wfa_prov_cmb. I get the result11/17/2008 instead of 2008-11-17? DDOBJ being a DATE field in table wfa_prov_cmb.
But the CAST works fine?
Looks like a similar issue. In most cases (e.g. ODBC / SQL Assistant) the client is responsible for formatting the output. If you want the DBMS to do it, CAST to [VAR]CHAR.Any of the following should work:SELECT CAST(CAST(DDOBJ as format 'YYYY-MM-DD') AS CHAR(10)) from wfa_prov_cmb;SELECT CAST((DDOBJ (format 'YYYY-MM-DD')) AS CHAR(10)) from wfa_prov_cmb;SELECT (DDOBJ (format 'YYYY-MM-DD'))(CHAR(10)) from wfa_prov_cmb;
I HAVE A COLUMN CHAR(6) WITH FORMAT 'MMDDYY', THIS COLUMN NEED CAST(COLUMN_DT AS DATE FORMAT 'YYYY-MM-DD'), SELECT FAILED: INVALID DATE SUPPLIED
SEL CAST(COLUMN_DT AS DATE FORMAT 'YYYY-MM-DD')
The syntax of the query seems fine. There seems to be some invalid data in the date column and thus failing the cast statement.
data entered is character: 062177 for test.
select cast(cast(dt as date format 'MMDDYY') as date format 'YYYY-MM-DD') from raja.test1
Again as said by Qaisar, check the valid dates :), since it is character.
Raja K Thaw
Street Children suffer not by their fault. We can help them if we want.