format with field

The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

format with field



i have problem with conversion of that sql syntax:


select StartDate = CONVERT(varchar(50),CONVERT(Date,dateadd(hh,0,'2018-02-11')),c.date_Type)

from [CampaignOperatorsDB].dbo.XXX_XXX_TimeZones "c"


i convert it like that:

SEL cast(CAST(cast('2018-02-11 00:00:00' AS TIMESTAMP(3)) as date FORMAT 'c.date_Type') AS VARCHAR(50)) StartDate from [CampaignOperatorsDB].dbo.XXX_XXX_TimeZones "c"


the problem is: when someone will change the field: 'c.date_Type' the type will be change.

i need help with write the correct code to dynamic field.




the out put suppos to be '11/02/2018'------varchar



thanks alot!




Junior Contributor

Re: format with field

If you want a date there's no need to cast a timestamp, simply use a literal:



DATE '2018-02-11' -- 'yyyy-mm-dd'
TIMESTAMP '2018-02-11 00:00:00' 'yyyy-mm-dd hh:mi:ss'
TIME '00:00:00' -- 'hh:mi:ss'



To support different data styles you could apply TO_CHAR/CASE:

To_Char(DATE '2018-02-11', 
      WHEN date_type = 3 THEN 'dd/mm/yy'
      WHEN date_type = 103 THEN 'dd/mm/yyyy'

Wrap it in a SQL-UDF when you need it multiple times...