Ruhaan Ruhaan - 5 months ago 22
SQL Question

Need part of a date

I am using SQL Server 2008R2.
I am trying to get part of a date in an output, and my column is in

datetime
datatype.
Eg,
If Current date and time is
2016-06-28 17:34:12.060
, then I need output as
17:00
only the Hour with
:00
mins.

I have tried this until now,

Select DateName(HH,SUBSTRING('2016-06-28 17:34:12.060',12,5)) +':00'


which gives me right output.But when I pass Column Name which is of
datetime
datatype, then it gives error,

Select DateName(HH,SUBSTRING(TimeInHour,12,5)) +':00'


gives error,


Argument data type time is invalid for argument 1 of substring function.


I know I am using
SUBSTRING()
at wrong place, But I really don't know how to achieve that output.
A help will be much appreciable.I need output as HH:00, Hour will be anything but 00 mins.

Answer

Why would you use substring() at all? The second argument to datename() should be a date/time data type. So, just do:

Select DateName(hour, '2016-06-28 17:34:12.060') + ':00'
Comments