Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to order by month which is varchar field according to date format? Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 6:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 4, 2013 1:08 AM
Points: 10, Visits: 33
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?
Post #1341196
Posted Tuesday, August 7, 2012 6:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 7, 2012 6:48 AM
Points: 14, Visits: 20
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
Post #1341201
Posted Tuesday, August 7, 2012 7:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:24 AM
Points: 5,489, Visits: 13,188
You can order by the datetime column, even if you don't include it in the select list.

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1341285
Posted Tuesday, August 7, 2012 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:36 PM
Points: 14,468, Visits: 38,067
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!
Post #1341287
Posted Tuesday, August 7, 2012 8:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:58 AM
Points: 3,217, Visits: 31,633
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
Post #1341300
Posted Tuesday, August 7, 2012 11:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 4, 2013 1:08 AM
Points: 10, Visits: 33
Thnx... J Livingston SQL it did worked...
Post #1341674
Posted Wednesday, August 8, 2012 12:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, February 24, 2016 3:37 AM
Points: 945, Visits: 695
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
Post #1341688
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse