Current Position:Home > All MonthNames and Month numbers in sql server

All MonthNames and Month numbers in sql server

Update:11-30Source: network consolidation
Advertisement
 
Hi All,
I want to display all months names and month numbers in a query.
 like        MName              
Mno
January               
1
February             
2
March                  
3
            December          
12
Regards,
Mandava.

The Best Answer

Advertisement
Kudos to all the posts with fancier and higher-tech approaches.  At the same time, this is a somewhat unique situation:  Unlike most things, we'll always have just 12 months, and that will never change.  This is an opportunity for "keep it
simple... Really simple", although the concept of using a VIEW for it is a little bit of lateral thinking.
I've also included two columns ("Mo" and "MMNum") that if you wanted to get technical, could be labeled "optional", and could also be computed instead of treated as literals.  At the same time, the extra fields may someday be highly convenient and allow
you to NOT compute the text-equivalent ("01" of the integer "1") someday, so why not?  Again, it's only 12 lines worth of typing, the data will never change, and sometimes, it's OK to use literals.
Create View MONTHLIST as
Select 1 as MM , 'January' as Mon , 'Jan' as Mo , '01' as MMNum
UNION
Select 2 as MM , 'February' as Mon , 'Feb' as Mo , '02' as MMNum
UNION
Select 3 as MM , 'March' as Mon , 'Mar' as Mo , '03' as MMNum
UNION
Select 4 as MM , 'April' as Mon , 'Apr' as Mo , '04' as MMNum
UNION
Select 5 as MM , 'May' as Mon , 'May' as Mo , '05' as MMNum
UNION
Select 6 as MM , 'June' as Mon , 'Jun' as Mo , '06' as MMNum
UNION
Select 7 as MM , 'July' as Mon , 'Jul' as Mo , '07' as MMNum
UNION
Select 8 as MM , 'August' as Mon , 'Aug' as Mo , '08' as MMNum
UNION
Select 9 as MM , 'September' as Mon, 'Sep' as Mo , '09' as MMNum
UNION
Select 10 as MM , 'October' as Mon , 'Oct' as Mo , '10' as MMNum
UNION
Select 11 as MM , 'November' as Mon , 'Nov' as Mo , '11' as MMNum
UNION
Select 12 as MM , 'December' as Mon , 'Dec' as Mo , '12' as MMNum
So, like everything else in SQL, there's always 50 ways to do something.  This is another one.
  • All MonthNames and Month numbers in sql server Update:11-30

      Hi All, I want to display all months names and month numbers in a query.  like        MName               Mno January                1 February              2 March                   3             December           12 Regards, Mandava.Kudos to all

  • Numbers of SQL Server CAL license required ? Update:11-30

    Hi, We are going to set up a SQL Server 2012 Server with 2 instances.  The first instance has 25 maximum number of concurrent users and the second one has 10 concurrent users. Does it mean that we need to purchase 25 + 10 = 35 CAL license for the set

  • Need to delete specific Months Data from SQL Server Table Update:10-11

    Greetings Everyone, So i have one table which contains 5 years old data, now business wants to keep just one year old data and data from qurter months i.e. (jan, mar, June, sep and December), i need to do this in stored procedure. how i can achive th

  • Sql server replication server SLOW Update:11-30

    My team has been troubleshooting this for months now.  Sql Server 2008 r2 Our Main DB & server is on "server A" and is a physical machine with a single virtual instance on it, it does not share with anyone, it has its own physical hard drive

  • Best method to determine the week number of a month - SQL Server 2012 Update:10-11

    Hi, I'm searching the most valid and tested method to determine the week number respect to a month. Fe, the 1st January falls in the 1st week of January, the 1st February fall in the 1st week of February, and so on. I've found many solutions but I'd

  • Month Display problem in OBI using SQL server Update:10-11

    Hi I am using SQL Server and in one of the reports that I am producing the month is displayed as CAL_MONTH APRIL AUGUST DECEMBER FEBRUARY JANUARY JULY JUNE MARCH MAY NOVEMBER OCTOBER SEPTEMBER instead of CAL_MONTH JANUARY FEBRUARY MARCh I used the Ev

  • Installed SQL Server with license key provided by Microsoft still it showing expiry date after 6 months Update:11-30

    I Installed Sql Server 2008 in my Windows 2008 Server everthing good but after installed when i check expiry date of SQL SERVER using query its showing after 180 Days my Sql will be expiry. Did i done any mistake while installed or do i want to any a

  • Numbering system in Sql Server Update:11-30

    hi i am using sql server 2008 & vs 2008 C# i have table [tbl_CashCounter]( [Id] [int] IDENTITY(1,1) NOT NULL, [BillNo] [nvarchar](50) NULL, [BillDate] [datetime] NULL i want BillNo  A0001,A0002,..........A9999. if it reached A9999 it should new serie

  • Apply monthly Windows 2008 r2 updates within SQL Server 2008 cluster Update:11-30

    Hello is applying Windows updates within 2 node SQL Server 2008 cluster (active-passive) as simple as download/install the windows update on the passive node, then failover, then apply on other node, and then fail back over ?...or are there other nee

  • SQL Server TechNet Guru News: October Winners Announced Update:10-11

    All the votes are in!  And below are the results for the TechNet Guru Awards, October 2014 !!!! For a full list of winners, see the full blog post, as runners up had to be removed from this post to fit the forum max length restrictions.  BizTalk Tech