Ruhaan Ruhaan - 1 year ago 69
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

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

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
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
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 Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download