|
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
|
|
|
August 18th, 2008, 06:07 AM
|
Authorized User
|
|
Join Date: May 2008
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
August 18th, 2008, 08:03 PM
|
Friend of Wrox
|
|
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
|
|
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
|
August 19th, 2008, 12:05 AM
|
Authorized User
|
|
Join Date: May 2008
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thank you Jeff:)
|
August 19th, 2008, 03:07 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
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.
|
August 19th, 2008, 08:27 PM
|
Friend of Wrox
|
|
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
|
|
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.
|
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
|
August 19th, 2008, 08:45 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
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
|
August 19th, 2008, 10:37 PM
|
Friend of Wrox
|
|
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
|
|
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
|
August 19th, 2008, 10:37 PM
|
Friend of Wrox
|
|
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
|
|
Quote:
quote:Originally posted by kalyanykk
Thank you Jeff:)
|
My pleasure... thank you for the feedback.
--Jeff Moden
|
|
|