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;