All Forums Database
NewAmigo 27 posts Joined 02/14
18 Feb 2014
SQL for Year and Month

Hello All,
I am fairly new to teradata and i am struck with some issue with the Sql on teradata 12
 
I have a Date dimension table with Year_of_Calender column which holds years eg:-2012, 2013 etc
and Month_Name column with name of the month eg:- Jan, Feb etc.
Basing on these two values i need to create a new column which shows Jan'12, Feb'13 etc, for some charting purpose.
I have tried using the Insert,Update and Substring function but i couldnt make it to work.
Can someone please shed some light on this, any sort of help is highly appreciated.
 
Kind Reagrds
Reddy

 

Adeel Chaudhry 773 posts Joined 04/08
18 Feb 2014

From what i understood .... you need to CAST both columns to VARCHAR and concatenate (using ||) to get the desired output.
 
CAST(Month_Name as VARCHAR(10)) || CAST(Year_of_Calender as VARCHAR(4))

 

HTH!

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
18 Feb 2014

Many Thanks Adeel, It has worked.
 
But i am interest in the last two parts of the year say '12 rather than 2012 for the year part.
 
Thanks

NewAmigo 27 posts Joined 02/14
18 Feb 2014

Apologies looking for something like "Jan'12"..."Feb'13"

Adeel Chaudhry 773 posts Joined 04/08
18 Feb 2014

You just need to substr like below:

CAST(Month_Name as VARCHAR(10)) || substr(CAST(Year_of_Calender as VARCHAR(4)), 3,2)
 
and in case you need ' .... you should use following:
 
CAST(Month_Name as VARCHAR(10)) || '''' || substr(CAST(Year_of_Calender as VARCHAR(4)), 3,2)

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
18 Feb 2014

Adeel you are a STAR mate, that has worked like a treat. Thanks a ton!!
 
As i have mentioned i am new to teradata or any other databases. can you please send a few reference docs or any other resources i can refer to?
 
Thanks again!!

natali 16 posts Joined 02/14
18 Feb 2014

can we also use substring in place of substr? i just posted a question regarding the same can you please answer that?

Natasha

Adeel Chaudhry 773 posts Joined 04/08
18 Feb 2014

Welcome Raja .... regarding material you can search on the web and visit www.teradata.com for details.
 
In my opinion practicing is the best option .... and you need to have a documentation handy to read and learn more about Teradata.

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
18 Feb 2014

Natasha, yes you can use them .... i'll reply on your post shortly.

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
18 Feb 2014

Hi Natasha,
in a calendar table there's a date column, too.
You might use it instead and simply apply a FORMAT: 

cast(cast(calendar_date as format 'mmm''yy') as char(7))

 

Dieter

jinli 10 posts Joined 11/12
20 Feb 2014

dnoeth demo'ed the neat format-way i ofen use.
this link may help -- http://teradataforum.com/l040409a.htm

You must sign in to leave a comment.