CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
Ruhaan
Ruhaan

Reputation: 174

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.

Upvotes: 0

Views: 91

Answers (5)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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'

Upvotes: 1

GuidoG
GuidoG

Reputation: 12039

select convert(varchar, datepart(hour, getdate())) + ':' + convert(varchar, datepart(second, getdate()))

Upvotes: 0

TaBi
TaBi

Reputation: 21

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

Upvotes: 1

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6566

Try this:

Select CAST(DATEPART(hour,'2016-06-28 17:34:12.060') AS VARCHAR(2)) +':00'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270421

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'

Upvotes: 5

Related Questions