SQL Server - order

Asked By farsha

26-Jun-07 08:19 AM

Hi,
There is a field called contractMonth in a table called table1.
The datatype of this field is varchar(50).
The data is something like:

Apr 2007
June 2008
Sep 2007
...

How is it possible to order this field.
For example I would like to retrieve something like the following:
Apr 2007
sep 2007
June 2008
...

Thanks

26-Jun-07 09:24 AM

...order by right(contractMonth, 4), contractMonth

26-Jun-07 08:31 AM

Hi
create table #t (c varchar(50))

insert into #t values  ('Apr 2007')
insert into #t values  ('June 2008')
insert into #t values  ('Sep 2007')
insert into #t values  ('Nov 2007')
insert into #t values  ('Feb 2006')

select * from #t order by convert(datetime,c,103) asc

26-Jun-07 08:39 AM

farshad,

Assuming that the character strings are _always_ valid month year
combinations, you could do the following:

SELECT contractMonth
FROM table1
ORDER BY CAST (contractMonth AS DATETIME)

Of course, I would not trust all my contractMonth values to be valid unless
some process was already assuring that.  A better method would be to make
the contractMonth a datetime column and just make them all fall on the first
day of the month.  It is better to leverage the native capabilities of the
database than try to fool with less well defined data.

RLF

26-Jun-07 09:16 AM

If the data can not be converted to datetime (for example Sep and
September are fine, but Sept will not convert) you may have to resort
to extreme measures, perhaps something like:

ORDER BY CASE WHEN contractMonth LIKE '%2000%' THEN 2000
WHEN contractMonth LIKE '%2001%' THEN 2001
WHEN contractMonth LIKE '%2002%' THEN 2002
WHEN contractMonth LIKE '%2003%' THEN 2003
WHEN contractMonth LIKE '%2004%' THEN 2004
WHEN contractMonth LIKE '%2005%' THEN 2005
WHEN contractMonth LIKE '%2006%' THEN 2006
WHEN contractMonth LIKE '%2007%' THEN 2007
WHEN contractMonth LIKE '%2008%' THEN 2008
WHEN contractMonth LIKE '%2009%' THEN 2009
END,
CASE WHEN contractMonth LIKE '%jan%' THEN  1
WHEN contractMonth LIKE '%feb%' THEN  2
WHEN contractMonth LIKE '%mar%' THEN  3
WHEN contractMonth LIKE '%apr%' THEN  4
WHEN contractMonth LIKE '%may%' THEN  5
WHEN contractMonth LIKE '%jun%' THEN  6
WHEN contractMonth LIKE '%jul%' THEN  7
WHEN contractMonth LIKE '%aug%' THEN  8
WHEN contractMonth LIKE '%sep%' THEN  9
WHEN contractMonth LIKE '%oct%' THEN 10
WHEN contractMonth LIKE '%nov%' THEN 11
WHEN contractMonth LIKE '%dec%' THEN 12
END

Roy Harvey
Beacon Falls, CT

On Tue, 26 Jun 2007 05:19:02 -0700, farshad

26-Jun-07 09:26 AM

Hi Roy

In my opinion  it would be better to 'fix' the data  to be converted to
datetime insead of building so long long query. Image if he has
to sort contracts from 1950.....
Just my two cents

26-Jun-07 10:53 AM

Forget this.
It was a holiday weekend in Quebec and I may have had a bit too much brew.
I should be back to normal tomorrow.

26-Jun-07 10:23 AM

On Tue, 26 Jun 2007 16:26:47 +0300, "Uri Dimant" <urid@iscar.co.il>


Of course it would be better to fix the data, if fixing the data (and
KEEPING it fixed!) is an option.  Of course a real fix would be to get
away from storing 'June 2008' and 'Sep 2007' in the first place and
use datetime.

Roy Harvey
Beacon Falls, CT