How to convert JAN 1, 2007 12:00:00 AM into timestamp format 'YYYY-MM-DDBHH:MI:SS' ???

Database
Enthusiast

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

6 REPLIES
Enthusiast

Re: How to convert JAN 1, 2007 12:00:00 AM into timestamp format 'YYYY-MM-DDBHH:MI:SS' ???

Hi,

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

Khurram
Enthusiast

Re: How to convert JAN 1, 2007 12:00:00 AM into timestamp format 'YYYY-MM-DDBHH:MI:SS' ???

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

Enthusiast

Re: How to convert JAN 1, 2007 12:00:00 AM into timestamp format 'YYYY-MM-DDBHH:MI:SS' ???

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
Junior Contributor

Re: How to convert JAN 1, 2007 12:00:00 AM into timestamp format 'YYYY-MM-DDBHH:MI:SS' ???

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

Enthusiast

Re: How to convert JAN 1, 2007 12:00:00 AM into timestamp format 'YYYY-MM-DDBHH:MI:SS' ???

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

Junior Contributor

Re: How to convert JAN 1, 2007 12:00:00 AM into timestamp format 'YYYY-MM-DDBHH:MI:SS' ???

Hi Shardul,

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