CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Penguen
Penguen

Reputation: 17298

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

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

Upvotes: 15

Views: 102182

Answers (9)

Rachel
Rachel

Reputation: 1296

In SQL Server 2012+, use the format function.

SELECT FORMAT(getdate(), 'HH:mm') as time

Docs

Upvotes: 0

Alex Purice
Alex Purice

Reputation: 43

SELECT CAST(CAST(GETDATE() AS Time(0)) AS VARCHAR(5))

Upvotes: 0

Satyaprakash Samantaray
Satyaprakash Samantaray

Reputation: 1

To get hh:mm format from today's date:

select getdate()

select convert(varchar(5),getdate(),108)

To get hh:mm format from any datetime column in a table data:

select date_time_column_name from table_name where column_name = 'any column data name'

select convert(varchar(5),date_time_column_name,108) from table_name
where column_name = 'any column data name'

select creationdate from employee where Name = 'Satya'

select convert(varchar(5),creationdate,108) from employee 
where Name = 'Satya'

Upvotes: 0

Jona
Jona

Reputation: 305

One way would be to use the RIGHT() function to crop the Date. Something like:

RIGHT(CONVERT(VARCHAR(8),Date, 108),5)

This will only work if number of characters is constant e.g. there is a leading zero if applicable. (Sorry havn't got SQL server here to test).

A better way is to use the T-SQL datepart function to split and then re-concatinate the date parts so:

DARTPART("hh", CONVERT(VARCHAR(8),Date, 108))+":"+DARTPART("mi", CONVERT(VARCHAR(8),Date, 108))

References:

http://msdn.microsoft.com/en-us/library/ms174420.aspx
http://msdn.microsoft.com/en-us/library/ms187928.aspx
http://msdn.microsoft.com/en-us/library/ms177532.aspx

Upvotes: 1

SQLMaster
SQLMaster

Reputation:

CONVERT(VARCHAR(5),Date, 108)-- Gets only HH:mm

Upvotes: 29

Quassnoi
Quassnoi

Reputation: 425833

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

Upvotes: 9

Keith
Keith

Reputation: 21264

SELECT Convert(varchar(5), GetDate(), 108)

Using varchar(5) will automatically truncate the date to remove the seconds.

Upvotes: 7

u07ch
u07ch

Reputation: 13702

I dont think there is a built in function; usually do something like this

SET @time = '07:45'
SET @date = CONVERT(DATETIME,@time)
SELECT @date

SELECT LEFT(CONVERT(VARCHAR,@date,108),5)

Upvotes: 3

Mehrdad Afshari
Mehrdad Afshari

Reputation: 422300

For this specific need you should use the between method as noted by Quassnoi's answer. However, the general problem can be solved with:

select dateadd(second, -datepart(second, @date), @date)

Upvotes: 2

Related Questions