April 4, 2013 at 7:55 am
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
April 4, 2013 at 8:09 am
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
April 5, 2013 at 10:38 am
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
Don Urquhart
April 5, 2013 at 10:39 am
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,':','.'))
April 5, 2013 at 10:55 am
order by convert(decimal(5,2),REPLACE(TestDate,':','.'))
Doesn't work for hours:min:sec either: Error converting data type varchar to numeric
Don Urquhart
April 5, 2013 at 11:22 am
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
Don Urquhart
April 5, 2013 at 11:27 am
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
April 5, 2013 at 1:00 pm
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.
April 5, 2013 at 1:09 pm
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;
April 8, 2013 at 8:55 am
Just in response to those who have mentioned time formats; this is hh:mm only.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy