Timestamp format not working to get the two digit year

Database
Enthusiast

Timestamp format not working to get the two digit year

Hi,

My Source data has only two digit year when i am trying to convert to timestamp

sel cast('12/12/12 00:00:00' as timestamp(0) format 'MM/DD/YYBHH:MI:SS')

o/p

12/12/1912 00:00:00

tried with the current_timestamp(0)

eg:

sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')

o/p

3/6/2013 12:09:59

i am getting "1912" as a year

Thanks

Balu

11 REPLIES
mjj
Teradata Employee

Re: Timestamp format not working to get the two digit year

Hi,

There is a dbscontrol parameter named Century Break.

If century break is 0 then all years in YY are 19YY

If century break is 100 then all years in YY are 20YY.

Enthusiast

Re: Timestamp format not working to get the two digit year

Thanks Mjj,

Could plz let me know how to over come this issue in teradata..

Junior Contributor

Re: Timestamp format not working to get the two digit year

A long time ago there was a so-called "year2k" problem :-)

The dbscontrol is a global setting, for this specific import you might either concat '20' to the two digit year or add 1200 months using ADD_MONTHS.

Dieter

Enthusiast

Re: Timestamp format not working to get the two digit year

HI BALU

  YOU CAN TRY THIS IN BTEQ UTITLITY ,IT GIVING EXPECTED O/P,I THINK THIS IS HELP FULL TO YOU

sel cast('12/12/12 00:00:00' as timestamp(0) format 'MM/DD/YYBHH:MI:SS');

 '12/12/12 00:00:00'

 --------------------------

   12/12/12 00:00:00

SK RAFI

Enthusiast

Re: Timestamp format not working to get the two digit year

 

HI,

I have one issue ,my table date is

id      name

101   xy

101   yz

102   ab

102   bc

i want out put like bellow table format

id      name

101   xy,yz

102  ab,bc

how its possible ,is there any way to solve this issue 

THANKS 

SK RAFI






Enthusiast

Re: Timestamp format not working to get the two digit year

Hi Doneth,

Concat worked !!

Thanks

Balaji

Enthusiast

Re: Timestamp format not working to get the two digit year

Hi SK RAFI,

One of the way to do it using OLAP function.

Following query would help you to understand the same.

May be you can write a recursive or stored procedure to meet your requirements on top of it.

SEL

t.id,

MAX( (CASE WHEN t1.rnk =1 THEN t1.name END) )

|| TRIM(CAST(',' AS CHAR(1))) ||

MAX( (CASE WHEN t1.rnk =2 THEN t1.name END) )

AS concat_name

FROM

tab1 t

INNER JOIN

(SEL

id

,name

, RANK () OVER (PARTITION BY id ORDER BY name) rnk

FROM tab1

) t1

ON t.id = t1.id

GROUP BY 1

Regards

Enthusiast

Re: Timestamp format not working to get the two digit year

Its working @Thanks a lot Simhadri
Enthusiast

Re: Timestamp format not working to get the two digit year

Its working @Thanks a lot Simhadri