Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Char(9) to Time Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 7:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:53 AM
Points: 51, Visits: 101
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
Post #1438813
Posted Thursday, April 4, 2013 8:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 12,876, Visits: 31,789
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1438822
Posted Friday, April 5, 2013 10:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:59 AM
Points: 105, Visits: 1,663
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



Output:
-03:06 -0.129167
0 0.000000
00:22 0.015278
00:39 0.027083
02:19 0.096528



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

Don Urquhart
Post #1439357
Posted Friday, April 5, 2013 10:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:04 AM
Points: 1,204, Visits: 2,194
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,':','.'))

Post #1439358
Posted Friday, April 5, 2013 10:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:59 AM
Points: 105, Visits: 1,663
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
Post #1439370
Posted Friday, April 5, 2013 11:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:59 AM
Points: 105, Visits: 1,663
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
Post #1439381
Posted Friday, April 5, 2013 11:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 12,876, Visits: 31,789
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1439384
Posted Friday, April 5, 2013 1:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:35 PM
Points: 22,992, Visits: 31,471
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

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1439435
Posted Friday, April 5, 2013 1:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:35 PM
Points: 22,992, Visits: 31,471
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1439440
Posted Monday, April 8, 2013 8:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:53 AM
Points: 51, Visits: 101
Just in response to those who have mentioned time formats; this is hh:mm only.
Post #1439887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse