## Char(9) to Time

 Author Message lanky_doodle Valued Member Group: General Forum Members Points: 64 Visits: 117 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:39I 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:06and descending; -03:06 02:19 00:39 00:220 Lowell SSChampion Group: General Forum Members Points: 14955 Visits: 38949 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.9999999so 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 SSC Veteran Group: General Forum Members Points: 219 Visits: 1969 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'unionselect '00:22' unionselect '00:39' unionselect '02:19' unionselect '-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 SortDatefrom #testwhere TestDate <> '0'order by SortDate`Output:-03:06 -0.1291670 0.00000000:22 0.01527800:39 0.02708302:19 0.096528 If you don't stand for something, you'll fall for anything!,Don Urquhart Andrew Watson-478275 SSCommitted Group: General Forum Members Points: 1657 Visits: 2653 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 SSC Veteran Group: General Forum Members Points: 219 Visits: 1969 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 SSC Veteran Group: General Forum Members Points: 219 Visits: 1969 This seems to work for all combinations of time values.`Create Table #test ( TestDate varchar(9) )Insert into #test Select '0'unionselect '01:00:22' unionselect '39:00' unionselect '02:19' unionselect '-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 SortDatefrom #testorder by SortDate` If you don't stand for something, you'll fall for anything!,Don Urquhart Lowell SSChampion Group: General Forum Members Points: 14955 Visits: 38949 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-Insane Group: General Forum Members Points: 24177 Visits: 37948 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 PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Lynn Pettis SSC-Insane Group: General Forum Members Points: 24177 Visits: 37948 And providing this info in this format would really help:`declare @TestData table ( tdid int identity(1,1), timedata char(9));insert into @TestDatavalues ('0'),('02:19'),('-03:06'),('00:22'),('00:39');select * from @TestData order by timedata asc;select * from @TestData order by timedata desc;` Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) lanky_doodle Valued Member Group: General Forum Members Points: 64 Visits: 117 Just in response to those who have mentioned time formats; this is hh:mm only.