Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hello Everyone,

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

SQL
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

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 :

SQL
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.
 
Share this answer
 
Note: Numeric conversion is must in this case for sorting AND insert data in proper format
see below example...
SQL
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
SQL
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
SQL
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!
:)
 
Share this answer
 
v6
Comments
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    
Also think about this case :
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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900