Cast

Analytics
Highlighted
Enthusiast

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?
8 REPLIES
Enthusiast

Re: Cast

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
Teradata Employee

Re: Cast

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 because
dateCol (FORMAT 'MM/YYYY') is old Teradata syntax for the inner CAST and
using BTEQ in field mode to generate a report effectively causes implicit CAST to [VAR]CHAR
Enthusiast

Re: Cast

Am I running into the same problem. When I run SELECT DDOBJ(format 'YYYY-MM-DD') from wfa_prov_cmb. I get the result

11/17/2008 instead of 2008-11-17? DDOBJ being a DATE field in table wfa_prov_cmb.
Enthusiast

Re: Cast

But the CAST works fine?
Teradata Employee

Re: Cast

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;

Fan

Re: Cast


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

 


Enthusiast

Re: Cast

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


Enthusiast

Re: Cast


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.