We help IT Professionals succeed at work.

Hoow to I convert month name to month number?

svasilakos
svasilakos asked
on
6,567 Views
Last Modified: 2008-01-09
This is kinda of silly but it's late.  I am getting the month name in my query using
DATENAME(month,t.transactiondate)

But I need to use this field the order by clause so I need it to sort by month number 01 -12 , not alphabetically.  How do I convert month name back to number so order by works?

Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jai STech Arch

Commented:
if t.transactiondate a database field
why dont you direcrly order by your transaction date ?

Author

Commented:
Oh.. good question. Becuase I am runing a larger query and converting to month name and than storing this in a #temp table that I actually run my report off of.

I think I'll do something similar to what angelIII said... I wasn't sure if there was a easy convert statement I was missing..

Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, Development

Commented:
I have used something like what was mentioned above but found that if I added datepart(month,fieldname) in addtion to DATENAME(month,fieldname) to the group by expression it works like a snap foe example to find the Number of Hires for a given month for each year .....

select
DATENAME(month,[HireDate])
as [Month],
datepart(year,[HireDate]) as [Year],
count(*) as [NumberHired]
from [AdventureWorks].[HumanResources].[Employee]
group by
datepart(year,[HireDate]),
datepart(month,[HireDate]),
DATENAME(month,[HireDate])

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.