Char(9) to Time

Question

Post reply

Char(9) to Time

lanky_doodle

SSCrazy

Points: 2378
More actions
April 4, 2013 at 7:55 am

#394522

Hey,
I have a column that's char(9) and effectively stores time. This is a sample of the data;
0
02:19
-03:06
00:22
00:39
I would like to be able to sort on this column, with minus values being first when ascending and last when descending, however it's not the case; minus numbers are last when ascending and first when descending. The above list in Ascending order would be;
0
00:22
00:39
02:19
-03:06
and descending;
-03:06
02:19
00:39
00:22
0

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

not sure what you can do yet;

two of the values cannot be directly converted to TIME datatype (the zero and negative values)

msdn says the possible values for TIME datatypes are from 00:00:00.0000000 through 23:59:59.9999999

so no negatives allowed.

With MyCTE (vartime)

AS

(

--SELECT '0' UNION ALL

SELECT '02:19' UNION ALL

--SELECT '-03:06' UNION ALL

SELECT '00:22' UNION ALL

SELECT '00:39'

)

select convert(time,vartime) as newtime

from MyCTE order by 1

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!

Don Urquhart SSCrazy Points: 2423 More actions · Answer 2

What about a calculated field based on the decimal portion of the internal time representation. Such as:

Create Table #test (

TestDate varchar(9)

)

Insert into #test

Select '0'

union

select '00:22'

union

select '00:39'

union

select '02:19'

union

select '-03:06'

Select TestDate,

case RTRIM(TestDate)

When '0' then 0

else

case left(TestDate,1)

when '-' then CONVERT(decimal(9,6),convert(datetime,substring(RTrim(TestDate),2,8),114)) * -1

else CONVERT(decimal(9,6),convert(datetime,RTrim(TestDate),114))

end

end as SortDate

from #test

where TestDate <> '0'

order by SortDate

[/code]

Output:

-03:06-0.129167

00.000000

00:220.015278

00:390.027083

02:190.096528

If you don't stand for something, you'll fall for anything!,

Don Urquhart

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 3

It's a bit of a bodge and wouldn't work if your times stretched to hh:mm:ss rather than just mm:ss, but this does the trick.

order by convert(decimal(5,2),REPLACE(colname,':','.'))

Don Urquhart SSCrazy Points: 2423 More actions · Answer 4

order by convert(decimal(5,2),REPLACE(TestDate,':','.'))

Doesn't work for hours:min:sec either: Error converting data type varchar to numeric

If you don't stand for something, you'll fall for anything!,

Don Urquhart

Don Urquhart SSCrazy Points: 2423 More actions · Answer 5

This seems to work for all combinations of time values.

Create Table #test (

TestDate varchar(9)

)

Insert into #test

Select '0'

union

select '01:00:22'

union

select '39:00'

union

select '02:19'

union

select '-03:06'

Select TestDate

,case left(TestDate,1)

when '-'

then Convert(decimal(9,8),Convert(datetime,Left('00:00:00', 9-Len(TestDate)) + substring(TestDate,2,8))) * -1

else

Convert(decimal(9,8),convert(datetime,Left('00:00:00', 8-Len(TestDate)) + TestDate))

end as SortDate

from #test

order by SortDate

If you don't stand for something, you'll fall for anything!,

Don Urquhart

Lowell SSC Guru Points: 323504 More actions · Answer 6

i think the OP should probably just convert the time to seconds, and use DATEADD whenever he needs to fiddle with the values.

at least , as an integer, you could more easily handle negative values accurately.

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!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Would also help to have more than one negative value in your sample data. Hard to tell how multiple negative values should be sorted along with the multiple non-negative values.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

And providing this info in this format would really help:

declare @TestData table (

tdid int identity(1,1),

timedata char(9)

);

insert into @TestData

values ('0'),('02:19'),('-03:06'),('00:22'),('00:39');

select * from @TestData order by timedata asc;

select * from @TestData order by timedata desc;

lanky_doodle SSCrazy Points: 2378 More actions · Answer 9

Just in response to those who have mentioned time formats; this is hh:mm only.