Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 18th, 2008, 06: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.


  #2 (permalink)  
Old August 18th, 2008, 08:03 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 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
  #3 (permalink)  
Old August 19th, 2008, 12: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:)

  #4 (permalink)  
Old August 19th, 2008, 03:07 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
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.
  #5 (permalink)  
Old August 19th, 2008, 08:27 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 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
  #6 (permalink)  
Old August 19th, 2008, 08:45 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
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
  #7 (permalink)  
Old August 19th, 2008, 10:37 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 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
  #8 (permalink)  
Old August 19th, 2008, 10:37 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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


My pleasure... thank you for the feedback.

--Jeff Moden


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 12:38 AM
month calendar bschleusner C# 2005 0 April 13th, 2007 09:46 PM
query Current Month, Month+1, Month+2, Month+3 anterior Access 2 September 24th, 2006 08:25 PM
Week number of the month kpshende SQL Server 2000 4 April 24th, 2006 05:29 PM
Days in a Month jmss66 Classic ASP Basics 3 April 11th, 2005 05:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.