How to order by month which is varchar field according to date format?

Question

Post reply

How to order by month which is varchar field according to date format?

naisu1383

Old Hand

Points: 380
More actions
August 7, 2012 at 6:39 am

#279923

I have month field in format as JAN-12 with data type varchar. Now i wan to order by this field converting it into date like jun-12 then jul-12. Currently it orders like jul-12 then jun-12 as it is in varchar format. So how can this be achieved?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rajprabuit Old Hand Points: 358 More actions · Answer 1

You can add another column 'YearMonth' in which u should store values like '201201','201202'....

If u do this, u can sort month field easily

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

You can order by the datetime column, even if you don't include it in the select list.

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 3

any solution is going to involve some sort of convert text to date , or a hard coded CASE statement int he order by.

can you fix the incoming data to return a datetime instead of the text version you are currently receiving?

this is a quick and dirty bandaid:

SELECT * FROM SOMETABLE

ORDER BY

CASE

WHEN monthfield LIKE 'jan%' THEN 1

WHEN monthfield LIKE 'feb%' THEN 2

WHEN monthfield LIKE 'mar%' THEN 3

WHEN monthfield LIKE 'apr%' THEN 4

WHEN monthfield LIKE 'may%' THEN 5

WHEN monthfield LIKE 'jun%' THEN 6

WHEN monthfield LIKE 'jul%' THEN 7

WHEN monthfield LIKE 'aug%' THEN 8

WHEN monthfield LIKE 'sep%' THEN 9

WHEN monthfield LIKE 'oct%' THEN 10

WHEN monthfield LIKE 'nov%' THEN 11

WHEN monthfield LIKE 'dec%' THEN 12

ELSE 13

END,monthfield

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

USE [tempdb]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[testmonths]') AND type in (N'U'))

DROP TABLE [dbo].[testmonths]

GO

CREATE TABLE [dbo].[testmonths](

[testMonth] [varchar](50) NULL

) ON [PRIMARY]

GO

INSERT INTO [dbo].[testmonths]([testMonth])

SELECT N'May-11' UNION ALL

SELECT N'May-12' UNION ALL

SELECT N'May-13' UNION ALL

SELECT N'Jun-12' UNION ALL

SELECT N'Jul-12' UNION ALL

SELECT N'Aug-13' UNION ALL

SELECT N'Jun-11' UNION ALL

SELECT N'Jul-11' UNION ALL

SELECT N'Aug-11' UNION ALL

SELECT N'Aug-12' UNION ALL

SELECT N'Jun-13' UNION ALL

SELECT N'Jul-13'

SELECT testMonth

FROM testmonths

ORDER BY CONVERT( datetime , '01-' + testMonth );

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

naisu1383 Old Hand Points: 380 More actions · Answer 5

naisu1383

Old Hand

Points: 380

August 7, 2012 at 11:02 pm

#1522951

Thnx... J Livingston SQL it did worked...

subbu1 Hall of Fame Points: 3281 More actions · Answer 6

pls try below code

declare @t1 table(id varchar(10))

insert into @t1(id)values('Jan-12'),('Feb-12'),('Mar-12'),('APR-12')

select *,convert(date,convert(varchar(10),'01'+'-'+id)) new from @t1

order by 2