SQLTeam.com | Weblogs | Forums

Get hour to key


#1

hi

i have a column with time like
....
2015-01-18 11:10:00.000
i want to get the key 11.0

2015-01-18 11:35:00.000
so the key is 11.5

this this the dimension:

key Hour
14.5 14:30-15:00
15.0 15:00-15:30
15.5 15:30-16:00
16.0 16:00-16:30
16.5 16:30-17:00
17.0 17:00-17:30


#2
SELECT cast(datepart(hour, cast('2015-01-18 11:35:00.000' AS DATETIME)) AS CHAR(2)) 
     + ':' 
     + cast(datepart(minute, cast('2015-01-18 11:35:00.000' AS DATETIME)) % 10 AS CHAR(1))