All Forums Database
sayaksitex 24 posts Joined 09/12
09 Oct 2013
How to convert JAN 1, 2007 12:00:00 AM into timestamp format 'YYYY-MM-DDBHH:MI:SS' ???

I have data coming from source as JAN 1, 2007 12:00:00 AM . In my target I have the column with datatype Timestamp(0).
How to cast JAN 1, 2007 12:00:00 AM to format 'YYYY-MM-DDBHH:MI:SS' to insert into the target?
 
Thanks,
Sayak

M.Saeed Khurram 544 posts Joined 09/12
09 Oct 2013

Hi,
What is the data type of source column? and is the source is Teradata database?
 

Khurram

sayaksitex 24 posts Joined 09/12
09 Oct 2013

Hi Saeed,
The source is actually a csv file. We have loaded it to teradata table with varchar() data type.
Now we need to load it to the target table with data type Timestamp(0) that is also in teradata.
Thanks,
Sayak Ghosh

M.Saeed Khurram 544 posts Joined 09/12
09 Oct 2013

In fact when you want to convert a string literal to date or timestamp, it requires the literal in 'YYYY-MM-DD HH:MI:SS' format. So we have to convert the source data into this format. I have created the query, I know it is not very efficient way but hope it will help you.

SELECT CAST(SUBSTRING('JAN 01, 2007 12:00:00' FROM  9 FOR 4) || '-' || CASE 
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'JAN' THEN '01'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'FEB' THEN '02'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'MAR' THEN '03'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'APR' THEN '04'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'MAY' THEN '05'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'JUN' THEN '06'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'JUL' THEN '07'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'AUG' THEN '08'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'SEP' THEN '09'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'OCT' THEN '10'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'NOV' THEN '11'
WHEN SUBSTRING('JAN 01, 2007 12:00:00' FROM  1 FOR 3) = 'DEC' THEN '12'
END||'-'|| SUBSTRING('JAN 01, 2007 12:00:00' FROM  5 FOR 2) ||' '|| SUBSTRING('JAN 01, 2007 12:00:00' FROM  14 FOR 8) AS TIMESTAMP(0)  FORMAT'YYYY-MM-DDBHH:MI:SS' )AS New_Date;

 

Khurram

dnoeth 4628 posts Joined 11/04
09 Oct 2013

Hi Sayakm,
Teradata's FORMAT doesn't support single digit days, but you can use this:

  CASE
     WHEN ts LIKE '_____,%' THEN SUBSTRING(ts FROM 1 FOR 4) || '0' || SUBSTRING(ts FROM 5)
     ELSE ts 
  END (TIMESTAMP(0), FORMAT 'mmmBdd,ByyyyBhh:mi:ssBt')

In TD14 there's a UDF supporting single digit day:

TO_TIMESTAMP(ts, 'mon dd, yyyy hh:mi:ss am')

Dieter
 

Dieter

sk8s3i 35 posts Joined 06/13
17 Oct 2014

Hi Dieter,
 
We are using TD14.
What if the source field is in SQL Server and conatains something like
Feb 14 2012  6:55AM
 
And this has to be loaded in a TIMESTAMP(6) field in a Teradata table.
 
Appreciate any pointers!
 
Regards,
Shardul

-Thanks Shardul

dnoeth 4628 posts Joined 11/04
18 Oct 2014

Hi Shardul,
try TO_TIMESTAMP(x, 'mon dd yyyy hh:miam')

Dieter

You must sign in to leave a comment.