How to get Month no from Month name

Posted by Patelpriyanka21 under Sql Server on 4/18/2013 | Points: 10 | Views : 2942 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hello Everyone
In my column value is january_2013,Feburary_2013...and till December_2103.
I want month number from this because i want to do order by on Month number.
I don't want to use case and i tried with left and len but not getting proper result.
Please suggest me how to solve this?

Thanks
Priyanka

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 4/18/2013 [Member] [MVP] Silver | Points: 25

0	CREATE TABLE FundaTable ( Column1 VARCHAR(50) ) GO INSERT FundaTable VALUES('january_2013') INSERT FundaTable VALUES('February_2013') INSERT FundaTable VALUES('June_2013') INSERT FundaTable VALUES('April_2013') INSERT FundaTable VALUES('May_2013') INSERT FundaTable VALUES('March_2013') GO SELECT Column1 [Without Order] FROM FundaTable GO Without Order ---------------------- january_2013 February_2013 June_2013 April_2013 May_2013 March_2013 SELECT Column1 [Ordered] FROM FundaTable ORDER BY CAST('01-' + Replace(Column1,'_','-') AS DATE) GO Ordered -------------- january_2013 February_2013 March_2013 April_2013 May_2013 June_2013 Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Patelpriyanka21, if this helps please login to Mark As Answer. \| Alert Moderator

CREATE TABLE FundaTable

(

Column1 VARCHAR(50)

)

GO



INSERT FundaTable VALUES('january_2013')

INSERT FundaTable VALUES('February_2013')

INSERT FundaTable VALUES('June_2013')

INSERT FundaTable VALUES('April_2013')

INSERT FundaTable VALUES('May_2013')

INSERT FundaTable VALUES('March_2013')

GO



SELECT Column1 [Without Order] FROM FundaTable

GO

Without Order

----------------------

january_2013

February_2013

June_2013

April_2013

May_2013

March_2013

SELECT Column1 [Ordered] FROM FundaTable

ORDER BY CAST('01-' + Replace(Column1,'_','-') AS DATE)

GO

Ordered

--------------

january_2013

February_2013

March_2013

April_2013

May_2013

June_2013

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Patelpriyanka21, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patelpriyanka21 on: 4/19/2013 [Member] Starter | Points: 25

0	Hi Bageshkumarbagi Thanks for reply but i want to solve this from sql query only. Priyanka Patelpriyanka21, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Patelpriyanka21 on: 4/19/2013 [Member] Starter | Points: 25

0	Hi Pandians Thanks for the reply i tried this query and am getting error Type DATE is not a defined system type. i am using sql server 2008. Thanks Priyanka Patelpriyanka21, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jayakumars on: 4/20/2013 [Member] [MVP] Bronze | Points: 25

0	hi try this code Select left(Column1,charIndex('_',Column1)),Replace(Column1,'_','') from FundaTable order by Column1 Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Patelpriyanka21, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Patelpriyanka21 on: 4/21/2013 [Member] Starter | Points: 25

0	Hi Jayakumars i want month name ascending wise means not A,B,C,...i want Jan,Feb,Mar.. and your code showing A,B,C,...sequence. Thanks Priyanka Patelpriyanka21, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jayakumars on: 4/22/2013 [Member] [MVP] Bronze | Points: 25

0	hi patel refer this http://www.sqlservercurry.com/2009/11/convert-month-number-to-month-name-in.html Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Patelpriyanka21, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts