SQL for Year and Month

Database

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




10 REPLIES
Teradata Employee

Re: SQL for Year and Month

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!

Re: SQL for Year and Month

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

Re: SQL for Year and Month

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

Teradata Employee

Re: SQL for Year and Month

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)

Re: SQL for Year and Month

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!!

N/A

Re: SQL for Year and Month

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

Teradata Employee

Re: SQL for Year and Month

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.

Teradata Employee

Re: SQL for Year and Month

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

N/A

Re: SQL for Year and Month

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))