How do I convert hh:mm:ss to hh:mm in SQL Server?

Question

How do I convert hh:mm:ss to hh:mm in SQL Server?

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

Answer 1

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