how to convert date format to 'DD-MM-YYYY' from YYYY-MM-DD HH24:MI: SS
I used cast function but is picking date format not custom
select CAST(PARTY_END_DT as date) from PARTY
OUTPUT as below but I need to show as DD/MM/YYYY
Solved! Go to Solution.
Use bteq and run below sql it will give you what you need , please don't use workbench or any other tool .
SELECT CAST(CAST(PARTY_END_DT AS DATE) AS DATE FORMAT 'DD/MM/YYYY');
I am new to Teradata, what is bteq ?? I am using Teradata SQL assistance
SELECT CAST(CAST(PARTY_END_DT AS DATE) AS DATE FORMAT 'DD-MM-YYYY') FROM PARTY;
it is not giving in the expected format.
If you are using SQL Assistant (SQLA) then what you're using will never work. That is because SQLA always has a date value returned as date data type, which it then displays using whatever formatting you've specified in SQLA options. (This is an area where you see the difference in what the dbms does compared to what a client tool - SQLA - does).
When using SQLA (or TD Studio) and you want the results of a FORMAT clause to be shown in your result window then you must CAST the formatted data to CHAR/VARCHAR. This bit is done by the dbms. Then the dbms sends back a character data type to the client tool - which simply displays whatever it has been sent.
You need something like:
SELECT CAST((PARTY_END_DT (FORMAT 'dd-mm-yyyy')) AS CHAR(10)) FROM PARTY;
As described above, this is telling the dbms to convert the date value to a CHAR, which in Teradata terms is 'formatting', and the FORMAT clause says how to format the date value. Now, the dbms is sending a character data type back to the client tool, which then simply displays it.