displaying different date format

Database
Highlighted

displaying different date format

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

 

my query

 

select CAST(PARTY_END_DT as date) from PARTY

 

OUTPUT as below but I need to show as DD/MM/YYYY

9/30/2017

 

Regards,

AP

 


Accepted Solutions
Junior Contributor

Re: displaying different date format

Or simply use

TO_CHAR(PARTY_END_DT, 'dd-mm-yyyy')
1 ACCEPTED SOLUTION
4 REPLIES

Re: displaying different date format

Hi ,

 

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');

 

Regards

Rahul

Re: displaying different date format

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.

 

Regards,

 

 

Senior Apprentice

Re: displaying different date format

Hi,

 

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.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: displaying different date format

Or simply use

TO_CHAR(PARTY_END_DT, 'dd-mm-yyyy')