0

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.

Ruhaan
  • 176
  • 13
  • Possible duplicate of [Time part of a DateTime Field in SQL](http://stackoverflow.com/questions/12354699/time-part-of-a-datetime-field-in-sql) – shA.t Jun 28 '16 at 11:11
  • yup possibly but not exactly – Ruhaan Jun 28 '16 at 11:13
  • 2
    There are a couple of options. You can [cast](https://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx) from one data type to another. Or you can make sure of SQL Server's [date and time functions](https://msdn.microsoft.com/en-GB/library/ms186724.aspx). – David Rushton Jun 28 '16 at 11:16
  • 1
    ...or you can make **use** of SQL Server's data and time functions... – David Rushton Jun 28 '16 at 11:22
  • thanks but now its working for me @destination-data – Ruhaan Jun 28 '16 at 11:27

5 Answers5

5

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'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It works for me fine when I put '2016-06-28 17:34:12.060',but its not exact date.I have a column name TimeInHour which contains list of dates from which I have to get the results.And the column is in datetime datatype – Ruhaan Jun 28 '16 at 11:15
1

Try this:

Select CAST(DATEPART(hour,'2016-06-28 17:34:12.060') AS VARCHAR(2)) +':00'
Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
1

Below is the code that might be helpful

SELECT CONVERT(VARCHAR(50),DATEPART(YY,'2016-06-28 17:34:12.060')) -- Year
SELECT CONVERT(VARCHAR(50),DATEPART(mm,'2016-06-28 17:34:12.060')) -- Month
SELECT CONVERT(VARCHAR(50),DATEPART(d,'2016-06-28 17:34:12.060'))  -- Day   
SELECT CONVERT(VARCHAR(50),DATEPART(HH,'2016-06-28 17:34:12.060'))+':00' -- Hour
SELECT CONVERT(VARCHAR(50),DATEPART(mi,'2016-06-28 17:34:12.060'))+':00' -- Minutes
SELECT CONVERT(VARCHAR(50),DATEPART(ss,'2016-06-28 17:34:12.060')) -- Seconds
SELECT CONVERT(VARCHAR(50),DATEPART(ms,'2016-06-28 17:34:12.060')) -- Millisecond
TaBi
  • 21
  • 3
1

You need to cast your DATETIME type column first, Use CAST function

Select DateName(HH,SUBSTRING(CAST(ColumnName AS VARCHAR(30)),12,5)) +':00'

Or alternative to do is Use LEFT and CONVERT

SELECT LEFT(CONVERT(VARCHAR, ColumnName ,108),2)+':00'
Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
0
select convert(varchar, datepart(hour, getdate())) + ':' + convert(varchar, datepart(second, getdate()))
GuidoG
  • 11,359
  • 6
  • 44
  • 79