Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 2 days ago @ 4:31 AM
Points: 5,014, Visits: 10,514
You can order by the datetime column, even if you don't include it in the select list.

--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1341285
Posted Tuesday, August 7, 2012 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 12,881, Visits: 31,815
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1341287
Posted Tuesday, August 7, 2012 8:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 1,886, Visits: 18,502
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 !
__________________________________________________________________
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, May 14, 2014 5:30 AM
Points: 880, Visits: 669
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