14,304,543 members
Rate this:
See more:
Hello Everyone,

I want to sort few records which actually contains Duration but they are in nvarchar datatype.. For eg.

```Select '33:14:14' as 'Duration'
UNION
Select '1:16:36' as 'Duration'
UNION
Select '0:3' as 'Duration'
UNION
Select '0:29' as 'Duration'
UNION
Select '02:12:39' as 'Duration'```

I want to sort them.. Unfortunately I am getting results in above format (like 0:3 which is wrong practice) but still is there any way to do so ?

Thanks,
Posted

Rate this:

## Solution 2

Hi,

One solution is converting your data to seconds either on the fly or by using a user defined function.
Here is a SQL statement that converts your data to seconds on the fly :

```SELECT * FROM (
SELECT Duration ,
CASE
WHEN CHARINDEX(':',Duration )=0 THEN
CAST (Duration AS DECIMAL(10,2))
WHEN CHARINDEX(':',Duration, CHARINDEX(':',Duration)+1) = 0 THEN
CAST(SUBSTRING(Duration, 1, CHARINDEX(':',Duration)-1) *60 AS DECIMAL(10,2))
+ CAST(SUBSTRING(Duration, CHARINDEX(':',Duration)+ 1 , LEN(Duration)) AS DECIMAL(10,2))
WHEN CHARINDEX(':',Duration, CHARINDEX(':',Duration)+1) >0  THEN
CAST(SUBSTRING(Duration, 1, CHARINDEX(':',Duration)-1) *3600.0 AS DECIMAL(10,2))
+ CAST(SUBSTRING(Duration,CHARINDEX(':',Duration)+ 1 , CHARINDEX(':',Duration , CHARINDEX(':',Duration)+1 ) - CHARINDEX(':',Duration) -1)*60  AS DECIMAL(10,2))
+ CAST(SUBSTRING(Duration,  CHARINDEX(':',Duration , CHARINDEX(':',Duration)+1 ) +1  , LEN(Duration)  ) AS DECIMAL(10,2))
ELSE 'unknown format'  END AS seconds
from
(
Select '33:14:14' as 'Duration'
UNION
Select '1:16:36' as 'Duration'
UNION
Select '0:3.1' as 'Duration'
UNION
Select '0:3' as 'Duration'
UNION
Select '0.03' as 'Duration'
UNION
Select '0:29' as 'Duration'
UNION
Select '0:31' as 'Duration'
UNION
Select '3:0' as 'Duration'
UNION
Select '0:29' as 'Duration'
UNION
Select '02:12:39' as 'Duration'
)
as a
) AS b
ORDER BY seconds```

Good Luck.
Rate this:

## Solution 1

Note: Numeric conversion is must in this case for sorting AND insert data in proper format
see below example...
```select dbo.SetFormat(duration) from
(
Select '33:14:14' as 'Duration'
UNION
Select '01:16:36' as 'Duration'
UNION
Select '0:3' as 'Duration'
UNION
Select '0:29' as 'Duration'
UNION
Select '02:12:39' as 'Duration'
)
as a
order by convert(decimal,replace( dbo.SetFormat(duration),':',''))```

create function
```Collapse | Copy Code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END```

Create this function also
```SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select dbo.Setformat('3:1')
CREATE FUNCTION Setformat
(
@a varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
select @a=sum(convert(int,t)) from(select  replicate('0',2-Len(val)) + val + replicate('0',((3-Id)*2)) as t from [dbo].[ParseValues](@a,':')) as a

return @a
END
GO```

Happy Coding!
:)
v6
Amir Mahfoozi 19-Dec-12 1:55am

Have a look at the result of this query :
SELECT Duration , convert(numeric,(replace(duration,':',''))) from
(
Select '33:14:14' as 'Duration'
UNION
Select '1:16:36' as 'Duration'
UNION
Select '0:3' as 'Duration'
UNION
Select '0:29' as 'Duration'
UNION
Select '0:31' as 'Duration'
UNION
Select '3:0' as 'Duration'
UNION
Select '0:29' as 'Duration'
UNION
Select '02:12:39' as 'Duration'
)
as a
order by convert(numeric,(replace(duration,':','')))
Aarti Meswania 19-Dec-12 1:59am

yes right can't use number
I have modified it thank you
Amir Mahfoozi 19-Dec-12 2:01am

Select convert(datetime,'1900-01-01 33:12:39') as 'Duration'
Aarti Meswania 19-Dec-12 2:03am

right
Aarti Meswania 19-Dec-12 2:15am

now you can check this

data should be in fixed format e.g hh:mm:ss

then it is easy to do sorting on it
Amir Mahfoozi 19-Dec-12 2:23am

According to this sample data type '33:14:14' in the question,
converting to datetime will always fail.
Amir Mahfoozi 19-Dec-12 2:27am

The OP clearly says :
Unfortunately I am getting results in above format (like 0:3 which is wrong practice)
So your query will not work correctly for this type of input :
Select '0:31' as 'Duration'
UNION
Select '3:1' as 'Duration'
Aarti Meswania 19-Dec-12 2:32am

yes in that case create need to vreate own function that convert that field value in to hh:mm:ss format first, then sorting will easy
Amir Mahfoozi 19-Dec-12 2:36am

I don't need that function.
Aarti Meswania 19-Dec-12 2:44am

I have modified last comment it was mistake writing sentence