Casting '01/07' to a date - 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 2005 Forums

Transact-SQL (2005)

Casting '01/07' to a date

Author

Topic

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2008-02-18 : 08:24:25

Hi All

My brain has truned to mush at the moment.
:(

I want to sort on a char(5) column with data in it that looks like

01/07
05/02
06/01
12/01
07/98

so it comes out in the human date order.
e.g

07/98
06/01
12/01
05/02
01/07

--
David

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-18 : 08:30:13

declare @a table (c1 varchar(20) )

insert into @a
select '01/07' union
select '05/02' union
select '06/01' union
select '12/01' union
select '07/98'

select * from @a
order by cast(left(c1,3)+'01/'+right(c1,2) as datetime)

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2008-02-18 : 08:40:55

Thanks jhocutt

I thought I had tried that.

I said my brain to mush.

--
David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 08:58:48

Make sure you use correct datatype for your columns.A proper datetime column would have made your job really easy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-18 : 09:12:15

quote:
Originally posted by jhocutt

declare @a table (c1 varchar(20) )

insert into @a
select '01/07' union
select '05/02' union
select '06/01' union
select '12/01' union
select '07/98'

select * from @a
order by cast(left(c1,3)+'01/'+right(c1,2) as datetime)

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

or

select * from @a
order by cast('01/'+c1 as datetime)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-18 : 09:13:12

quote:
Originally posted by the1gadget

Hi All

My brain has truned to mush at the moment.
:(

I want to sort on a char(5) column with data in it that looks like

01/07
05/02
06/01
12/01
07/98

so it comes out in the human date order.
e.g

07/98
06/01
12/01
05/02
01/07

--
David

Always use proper DATETIME datatype to store dates
If you want to store month and year and store it as first day of the month in DATETIME column

Madhivanan

Failing to plan is Planning to fail

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2008-04-02 : 05:30:58

Hi Madhivanan

Just to say that I don't have any control over the date time as it wa comming for a source that I did not have any control over.

--
David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources