SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Char(9) to Time


Char(9) to Time

Author
Message
lanky_doodle
lanky_doodle
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 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: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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28770 Visits: 39983
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
Don Urquhart
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 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'
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
Andrew Watson-478275
Andrew Watson-478275
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1765 Visits: 2704
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
Don Urquhart
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 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
Don Urquhart
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 1969
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28770 Visits: 39983
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
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40462 Visits: 38567
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.

Cool
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)
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40462 Visits: 38567
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;




Cool
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)
lanky_doodle
lanky_doodle
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 117
Just in response to those who have mentioned time formats; this is hh:mm only.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search