Solved

computed column for finding the numerical month

Posted on 2014-12-14
3
94 Views
Last Modified: 2014-12-14
I have  a column named Month in my table called tblOrgOrganizing.

The values are January, February, March, etc....

I would like to add a second computed column that will give a numerical equivalent to the month as below:

January = 1
February = 2

How can I accomplish this?
0
Comment
Question by:al4629740
  • 2
3 Comments
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
select datepart(MM,[YourMonthColumName] + ' 1 2000')
returns 1

e.g.
select datepart(MM,'January' + ' 1 2000')
returns 1
select datepart(MM,'February' + ' 1 2000')
returns 2

To add as a computed column via TSQL:
ALTER TABLE dbo.tblOrgOrganizing ADD MonthNumber AS  datepart(MM,[YourMonthColumName] + ' 1 2000');

Or, to add via SSMS, see this link http://msdn.microsoft.com/en-us/library/ms188300.aspx
0
 

Author Comment

by:al4629740
Comment Utility
This statement will add the new column and identify the numerical value?

ALTER TABLE dbo.tblOrgOrganizing ADD MonthNumber AS  datepart(MM,[NumericalMonth] + ' 1 2000');
0
 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points
Comment Utility
Sorry, I didn't read your post carefully enough. Your varchar column is called 'Month', so I could have named it properly in my previous example. I also assumed your schema was dbo.

It should be

ALTER TABLE tblOrgOrganizing ADD MonthNumber AS  datepart(MM,Month + ' 1 2000');

I just tested this and it works on 2008R2 (adds the column and computes the numeric value of the month).
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now