All Forums Analytics
gavakie 9 posts Joined 12/08
02 Dec 2008
Cast

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?

leo.issac 184 posts Joined 07/06
02 Dec 2008

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

Fred 583 posts Joined 08/04
02 Dec 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

krisnkev 5 posts Joined 11/08
05 Dec 2008

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.

krisnkev 5 posts Joined 11/08
05 Dec 2008

But the CAST works fine?

Fred 583 posts Joined 08/04
06 Dec 2008

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;

reorz 1 post Joined 04/14
03 Apr 2014

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')
FROM DATABASE.TABLE

 

04 Apr 2014

The syntax of the query seems fine. There seems to be some invalid data in the date column and thus failing the cast statement.

Raja_KT 1132 posts Joined 07/09
04 Apr 2014

data entered is character: 062177 for test.

 

select cast(cast(dt as date format 'MMDDYY') as date format 'YYYY-MM-DD') from raja.test1

 

result: 1977-06-21

 

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.

You must sign in to leave a comment.