Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 18th, 2008, 07:07 AM
Authorized User
 
Join Date: May 2008
Location: Hyderabad, AP, India.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to kalyanykk
Default Converting month name to month number

Hi All,
       I want to convert a month name to month number in sql2005.

For example: My query returns july as 'JUL' and august as 'AUG'.
Now my problem is i have to show 'JUL' as '7' and august as '8' in
one report.
I dont want to use case statement. Is there any built in function for this.


Reply With Quote
  #2 (permalink)  
Old August 18th, 2008, 09:03 PM
Friend of Wrox
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 473
Thanks: 0
Thanked 7 Times in 7 Posts
Default

Replace 'JUL' with the part of your query that returns the 3 letter month name...

 SELECT DATEPART(mm,CAST('JUL'+ ' 1900' AS DATETIME))

--Jeff Moden
Reply With Quote
  #3 (permalink)  
Old August 19th, 2008, 01:05 AM
Authorized User
 
Join Date: May 2008
Location: Hyderabad, AP, India.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to kalyanykk
Default

Thank you Jeff:)

Reply With Quote
  #4 (permalink)  
Old August 19th, 2008, 04:07 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

If you are getting the month name via a SQL query in the first place, maybe it would be better to change the original query.

Converting a datetime value to a string and then back to a datetime and then finally to an integer is expensive. Not TOO expensive, but still...

If you showed us the original query, we might be able to make this more efficient.
Reply With Quote
  #5 (permalink)  
Old August 19th, 2008, 09:27 PM
Friend of Wrox
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 473
Thanks: 0
Thanked 7 Times in 7 Posts
Default

 
Quote:
quote:If you are getting the month name via a SQL query in the first place, maybe it would be better to change the original query.
Quote:
Heh... I was gonna say "Wha'? Are you crazy? Who would do that?"... then I remembered how real life goes.:D

I agree, we need to see the original query or a better explanation as to where the 3 character month name comes from.

--Jeff Moden
Reply With Quote
  #6 (permalink)  
Old August 19th, 2008, 09:45 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Yours was an interesting way of doing this. If I had thought of using CAST, I probably would have done
     SELECT DATEPART(mm,CAST(monthname+ ' 1, 1900' AS DATETIME))
I didn't realize you could use a partial data (month and year only) in a CAST expression.

By the by...

Another way to do this would be:
   SELECT CHARINDEX(monthname,'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber
Reply With Quote
  #7 (permalink)  
Old August 19th, 2008, 11:37 PM
Friend of Wrox
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 473
Thanks: 0
Thanked 7 Times in 7 Posts
Default

Try CAST('2015' AS DateTime) and see what happens... it's why the ISDATE function is a bit fickle...

... Dunno because I haven't tested it on a million rows, yet, but I'm thinking the CharIndex/3 method will beat the Cast method because there's no conversions... nicely done.

--Jeff Moden
Reply With Quote
  #8 (permalink)  
Old August 19th, 2008, 11:37 PM
Friend of Wrox
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 473
Thanks: 0
Thanked 7 Times in 7 Posts
Default

Quote:
quote:Originally posted by kalyanykk
 Thank you Jeff:)


My pleasure... thank you for the feedback.

--Jeff Moden
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
getting the second saturday of every month haleem .NET Framework 2.0 1 May 3rd, 2007 01:38 AM
month calendar bschleusner C# 2005 0 April 13th, 2007 10:46 PM
query Current Month, Month+1, Month+2, Month+3 anterior Access 2 September 24th, 2006 09:25 PM
Week number of the month kpshende SQL Server 2000 4 April 24th, 2006 06:29 PM
Days in a Month jmss66 Classic ASP Basics 3 April 11th, 2005 06:57 PM



All times are GMT -4. The time now is 09:06 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.