order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

order

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-06-26 : 08:17:02

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-26 : 08:25:02

[code]-- prepare sample data
declare @t table
(
data varchar(50)
)

insert @t
select 'Apr 2007' union all
select 'June 2008' union all
select 'Sep 2007'

-- Desired output
Select *
from @t
order by cast('01-' + replace(data, ' ', '-') as datetime)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-06-26 : 08:29:26

Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 08:38:09

1 Always use proper datatype DATETIME to store dates and let front end do the formation

2 Order by cast('01 '+datecol as datetime)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 08:39:50

quote:

Originally posted by harsh_athalye

-- prepare sample data
declare @t table
(
	data varchar(50)
)

insert @t
select 'Apr 2007' union all
select 'June 2008' union all
select 'Sep 2007'

-- Desired output
Select *
from @t
order by cast('01-' + replace(data, ' ', '-') as datetime)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

You dont need replace. See if cast(data as datetime) also works

Madhivanan

Failing to plan is Planning to fail

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-06-26 : 09:00:39

quote:

Originally posted by harsh_athalye

-- prepare sample data
declare @t table
(
	data varchar(50)
)

insert @t
select 'Apr 2007' union all
select 'June 2008' union all
select 'Sep 2007'

-- Desired output
Select *
from @t
order by cast('01-' + replace(data, ' ', '-') as datetime)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

get this error now:
Conversion failed when converting datetime from character string.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 09:01:29

What about other solutions?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources