[sql] Get Hours and Minutes (HH:MM) from date

I want to get only hh:mm from date.

How I can get this?

I have tried this :

CONVERT(VARCHAR(8), getdate(), 108)

You can cast datetime to time

select CAST(GETDATE() as time)

If you want a hh:mm format

select cast(CAST(GETDATE() as time) as varchar(5))

If you want to display 24 hours format use:

SELECT FORMAT(GETDATE(),'HH:mm')

and to display 12 hours format use:

SELECT FORMAT(GETDATE(),'hh:mm')

Here is syntax for showing hours and minutes for a field coming out of a SELECT statement. In this example, the SQL field is named "UpdatedOnAt" and is a DateTime. Tested with MS SQL 2014.

SELECT Format(UpdatedOnAt ,'hh:mm') as UpdatedOnAt from MyTable

I like the format that shows the day of the week as a 3-letter abbreviation, and includes the seconds:

SELECT Format(UpdatedOnAt ,'ddd hh:mm:ss') as UpdatedOnAt from MyTable

The "as UpdatedOnAt" suffix is optional. It gives you a column heading equal tot he field you were selecting to begin with.

You can easily use Format() function instead of all the casting for sql 2012 and above only

SELECT FORMAT(GETDATE(),'hh:mm')

This is by far the best way to do the required conversion.

Following code shows current hour and minutes in 'Hour:Minutes' column for us.

SELECT  CONVERT(VARCHAR(5), GETDATE(), 108) + 
(CASE WHEN DATEPART(HOUR, GETDATE()) > 12 THEN ' PM'
    ELSE ' AM'
END) 'Hour:Minutes'

or

SELECT  Format(GETDATE(), 'hh:mm') + 
(CASE WHEN DATEPART(HOUR, GETDATE()) > 12 THEN ' PM'
    ELSE ' AM'
END) 'Hour:Minutes'

Another method using DATEPART built-in function:

SELECT cast(DATEPART(hour, GETDATE()) as varchar) + ':' + cast(DATEPART(minute, GETDATE()) as varchar)

The following works on 2008R2+ to produce 'HH:MM':

select
case
when len(replace(replace(replace(right(cast(getdate() as varchar),7),'AM',''),'PM',''),' ','')) = 4
then '0'+ replace(replace(replace(right(cast(getdate() as varchar),7),'AM',''),'PM',''),' ','')
else replace(replace(replace(right(cast(getdate() as varchar),7),'AM',''),'PM',''),' ','') end as [Time]