17: 00 PM

17: 00 PM, really? I always thought US time are up to 12, not above?

SELECT FORMAT(cast('2017-08-17 15:31:18.217' as datetime),'hh:mm')

You can try the following using length of column value and case statement.

Declare @YourTable table (SomeCol int)
Insert into @YourTable values
 (2130)
,(103)
,(10)
,(1)

Select 
    case LEN(SomeCol)
        when 4 then SUBSTRING('2130', 1, 2) + ':' + SUBSTRING('2130', 3, 2)
        when 3 then '0' + SUBSTRING('103', 1, 1) + ':' + SUBSTRING('103', 2, 2)
        when 2 then '00:' + CONVERT(Varchar(5), SomeCol)
        else '00:0' + CONVERT(Varchar(5), SomeCol)
    end as TimeValue
from @YourTable

try this :

select cast((substring('180000',0,3)+':'+substring('180000',3,2)+':'+substring('180000',5,2)) as time)

If you are using SQL Server 2012 or above and want to store the values as actual TIME data types, you can use the TIMEFROMPARTS function.

For example:

DECLARE @d VARCHAR(10)
SET @d = '151200'

SELECT TIMEFROMPARTS(LEFT(@d,2), SUBSTRING(@d,3,2), RIGHT(@d,2), 0)

This will return a TIME. For more information, please see this: https://msdn.microsoft.com/en-us/library/hh213398.aspx

You can also make your string as hh:mi:ss:mmm (24 hour format) and then convert as time. That means trimming out last 3 digits from your milli seconds without replacing last ':' with '.'

Here is MSDN link for more details of formatting. Here is working example of SQL Fiddle

DECLARE @s varchar(50) = '09:47:11:895799'
SELECT Convert(time, Left(@s,Len(@s)-3), 114)

In general, the set of timestamps is not well-ordered, this means you cannot get a "last" timestamp whose time part up to minutes is 2009-05-06 14:58.

In SQL Server, which keeps the time part of a datetime as a number of 1/300 second fractions after midnight, this "last" timestamp would be 2009-05-06 14:58:59.997, but this is not guaranteed to be compatible with future releases of with other TIMESTAMP storage methods.

That means you'll need to split your BETWEEN condition into two conditions, one of which being strict less than the next minute:

select Count(Page) as VisitingCount,Page,CONVERT(VARCHAR(8),Date, 108) from scr_SecuristLog   
where Date >= '2009-05-04 00:00:00'
      AND Date < DATEADD(minute, 1, '2009-05-06 14:58')
and [user] in(select USERNAME             
    from scr_CustomerAuthorities )  
group by Page,Date order by [VisitingCount] asc

This solution will efficiently use indexes on Date

You can convert those values to time and for summing you could simply get their difference in seconds from midnight and sum. Result would be in seconds. ie:

DECLARE @times TABLE(t TIME(0));
INSERT INTO @times(t)VALUES('03:20:30'), ('02:10'), ('01:00');

SELECT SUM(DATEDIFF(SECOND, '00:00:00', t))FROM @times AS t;

Try below:

SELECT CONVERT(VARCHAR(20), GETDATE(), 101)

If your time amount exceeds 24 hours it won't be handled correctly with the DATEADD and CONVERT methods.

SELECT CONVERT(varchar, DATEADD(ms, 24*60*60 * 1000, 0), 114)
00:00:00:000

The following function will handle times exceeding 24 hours (~max 35,791,394 hours).

create function [dbo].[ConvertTimeToHHMMSS]
(
    @time decimal(28,3), 
    @unit varchar(20)
)
returns varchar(20)
as
begin

    declare @seconds decimal(18,3), @minutes int, @hours int;

    if(@unit = 'hour' or @unit = 'hh' )
        set @seconds = @time * 60 * 60;
    else if(@unit = 'minute' or @unit = 'mi' or @unit = 'n')
        set @seconds = @time * 60;
    else if(@unit = 'second' or @unit = 'ss' or @unit = 's')
        set @seconds = @time;
    else set @seconds = 0; -- unknown time units

    set @hours = convert(int, @seconds /60 / 60);
    set @minutes = convert(int, (@seconds / 60) - (@hours * 60 ));
    set @seconds = @seconds % 60;

    return 
        convert(varchar(9), convert(int, @hours)) + ':' +
        right('00' + convert(varchar(2), convert(int, @minutes)), 2) + ':' +
        right('00' + convert(varchar(6), @seconds), 6)

end

Usage:

select dbo.ConvertTimeToHHMMSS(123, 's')
select dbo.ConvertTimeToHHMMSS(96.999, 'mi')
select dbo.ConvertTimeToHHMMSS(35791394.999, 'hh')
0:02:03.000
1:36:59.940
35791394:59:56.400

And to do it by SQL:

SELECT FORMAT( getdate(), 'HH:mm', 'en-US' )