Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Hoow to I convert month name to month number?

Posted on 2007-10-03
4
Medium Priority
?
6,548 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?

0
Comment
Question by:svasilakos
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 750 total points
ID: 20012168
you need to create a function yourself to "translate" the month name into the numerical value.
I usually have a table with the translation values, so I can join/lookup in there
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20012179
if t.transactiondate a database field
why dont you direcrly order by your transaction date ?
0
 

Author Comment

by:svasilakos
ID: 20012201
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..

0
 
LVL 9

Expert Comment

by:Tone' Shelby
ID: 20527487
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])
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

572 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