Appending Month Number to month name

Question

Hi,

I have a column name "Month", which displays data as Jan, feb, mar ....Dec etc.

I need to add another column "Month1" which should display data as "1Jan, 2Feb, 3Mar....12Dec".

Basically its not a update to the table, my query should display both the columns "Month" and "Month1"

Thank you.

Answer 1

And?

Where is a code which you was able to develop and where are you stocked? 

---

Sincerely, Highly skilled coding monkey.

Answer 2

Sorry didnt understand...basically i am looking for a query for my question :-)

Answer 3

This should work for you

select month,
      case MONTH when 'Jan' then '1'
                 when 'Feb' then '2'
                 -- and so on
      end +  MONTH as month1
from yourTable

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

Hi,

Thanks for your reply. I tired this. The problem is the "Month" column is not a part of the table. Its a view. so when i am trying how u mentioned above, then it says "Month" is not a valid column.

Answer 5

Hi,

Thanks for your reply. I tired this. The problem is the "Month" column is not a part of the table. Its a view. so when i am trying how u mentioned above, then it says "Month" is not a valid column.

select other columns...,
      month,
      case MONTH when 'Jan' then '1'
                 when 'Feb' then '2'
                 -- and so on
      end +  MONTH as month1
from (your existing query)q

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 6

I highly suggest you create a calendar table, and add a display column to display the way you want and calculate the data once.

https://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

Answer 7

I am trying to save my query as view first and then write as above, but the problem, is that my query is having variables declared to convert month from date and view cant have any variables.

Answer 8

I am trying to save my query as view first and then write as above, but the problem, is that my query is having variables declared to convert month from date and view cant have any variables.

Why do you need variables?

You can use GETDATE() function to get date value and extract month information from it

If you want query to be parameter driven then what you need is stored procedure and not a view

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 9

Hi,

Thank you..Since I am new to Store Procedure, I created a view first which give me Month name and month number then I concatenate both in another view and I could write my main query including the 2nd view to get the info I need. thanks.

I used below to get the Month name and Month number

convert(char(3), ReceiptDate, 0) As Month_Name,

datepart(month,ReceiptDate) AS Month_No

Answer 10

Hi,

Thank you..Since I am new to Store Procedure, I created a view first which give me Month name and month number then I concatenate both in another view and I could write my main query including the 2nd view to get the info I need. thanks.

I used below to get the Month name and Month number

convert(char(3), ReceiptDate, 0) As Month_Name,

datepart(month,ReceiptDate) AS Month_No

No need to create a view just for this purpose

You can always do this inline on your SELECT statement itself inside your procedure if you want

i.e like

SELECT ....,
convert(char(3), ReceiptDate, 0) As Month_Name,

datepart(month,ReceiptDate) AS Month_No,
...

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page