format with field

Database
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.
Enthusiast

format with field

Hi,

 

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!

 

 

 

1 REPLY
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', 
   CASE
      WHEN date_type = 3 THEN 'dd/mm/yy'
      WHEN date_type = 103 THEN 'dd/mm/yyyy'
   END)

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