All Forums Database
balu_td 22 posts Joined 03/13
06 Mar 2013
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
 

mjj 23 posts Joined 03/10
07 Mar 2013

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.
 

balu_td 22 posts Joined 03/13
07 Mar 2013

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

dnoeth 4628 posts Joined 11/04
07 Mar 2013

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

Dieter

skrafi 6 posts Joined 10/11
07 Mar 2013

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

skmdrf

skrafi 6 posts Joined 10/11
07 Mar 2013

 

 
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

 

 

skmdrf

balu_td 22 posts Joined 03/13
07 Mar 2013

Hi Doneth,
Concat worked !!
Thanks
Balaji

simhadrijk 18 posts Joined 04/10
08 Mar 2013

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
 

Jagdish Kumar Simhadri

skrafi 6 posts Joined 10/11
08 Mar 2013

Its working @Thanks a lot Simhadri

skmdrf

skrafi 6 posts Joined 10/11
08 Mar 2013

Its working @Thanks a lot Simhadri

skmdrf

09 Mar 2013

Hi SF Rafi,
I belive you can get the similar Output using the Recursive Queries..
 
thanks!
Nishant Bhardwaj

10 Mar 2013

Hi Rafi,
Yes, as Nishant pointed out an approach using recursive queries would be a more generic one
here is an eaxmple-
Assuming tb1 is the table containing data we need to make tb3 to rank the columns and then we can
use recrsive query to come up with desired output using recursive queries we need not have the prior
knowledge of the number of names realted to each id it could be varying like 2, or 3
create multiset volatile table tb1
(id integer
,nm varchar(5)
)   primary index (id,nm)  on commit preserve rows;
 
 
insert into tb1 values (10,'xy');
insert into tb1 values (10,'yz');
insert into tb1 values (10,'zx');
insert into tb1 values (20,'ab');
insert into tb1 values (20,'bc');

create multiset volatile table tb3
as
(
sel
id
,nm
,rank () over (partition by id order by nm) rn1
from
tb1
) with data primary index (id,nm) on commit preserve rows;

with recursive rslt (id,nm,rn1,lvl)
as(
sel
id,
cast ( nm  as varchar(20)),
rn1,
0 as lvl
from
tb3
where
rn1 = 1
union all
sel
rslt.id,
rslt.nm || b.nm,
b.rn1,
rslt.lvl +1 as lvl
from
tb3  b
inner join
rslt
on
rslt.id = b.id
where
rslt.rn1 < b.rn1
)
 sel id,nm
 from rslt
 qualify rank() over (partition by id  order by lvl desc ) = 1;
 
Regards,
R.Rajeev
 

You must sign in to leave a comment.