Teradata date function

saidvs · Post by **saidvs** » Thu Sep 06, 2007 3:13 pm

Hi,

I am new to Teradata and have a problem converting date into string.
actually date is stored as varchar in teradata ex: 07/24/1922 fromat
and i want to convert into '19220724'. Please help me...

thanks,
Venkat

ray.wurlod · Post by **ray.wurlod** » Thu Sep 06, 2007 4:31 pm

Surely you can just use some combination of SUBSTRING and concatenation to produce your desired result in a SELECT statement?

In ANSI SQL this would be

Code: Select all

SELECT SUBSTRING(DATESTR FROM 7 FOR 4) || SUBSTRING(DATESTR FROM 1 FOR 2) || SUBSTRING(DATESTR FROM 4 FOR 2) FROM tablename

keshav0307 · Post by **keshav0307** » Wed Sep 12, 2007 1:08 am

YOu search in Teradata forum.

anyway you can use this

CAST(CAST(<Date Column> AS DATE FORMAT 'YYYYMMDD' ) AS CHAR( 8 ))

e.g.
SEL CAST(CAST(CURRENT_DATE AS DATE FORMAT 'YYYYMMDD' ) AS CHAR( 8 ))

hamzaqk · Post by **hamzaqk** » Wed Sep 12, 2007 5:48 am

i am not sure who told you that teradata stored dates in varchar because it does not !! it stores them in an integer form always integer 4 that is

and for what you want try this:

cast ('07/24/1922' as date format 'yyyymmdd')

Teradata Certified Master V2R5