know in which interval of dates of 15 minutes is a date SQL SERVER

user_5292714 0
Viewed 104

sql fiddle example

I have this table structure :

CREATE TABLE TIMETABLE
    ([ID] int, [Name] varchar(50), [StartDate] datetime, [EndDate] datetime)
;

INSERT INTO TIMETABLE
    ([ID], [Name], [StartDate], [EndDate])
VALUES
    (1, 'John',  '2017-01-29 16:00:00.000', '2017-01-29 16:12:00.000'),
    (2, 'Mario',  '2017-01-29 16:17:00.000', '2017-01-29 16:29:00.000'),
    (3, 'Kate',  '2017-01-15 10:35:00.000', '2017-01-15 10:40:00.000'),
    (4, 'Maria', '2017-01-15 10:17:00.000', '2017-01-15 10:27:00.000'),
    (5, 'Oliver',  '2017-01-15 13:46:00.000', '2017-01-29 14:00:00.000')
;

And The result for this :

select * from TIMETABLE

ID  Name    StartDate               EndDate
1   John    2017-01-29T16:00:00Z    2017-01-29T16:12:00Z
2   Mario   2017-01-29T16:17:00Z    2017-01-29T16:29:00Z
3   Kate    2017-01-15T10:35:00Z    2017-01-15T10:40:00Z
4   Maria   2017-01-15T10:17:00Z    2017-01-15T10:27:00Z
5   Oliver  2017-01-15T13:46:00Z    2017-01-29T14:00:00Z

I want to know with a range from 15 mins in wich range is the date, for example:

   ID   Name    StartDate               EndDate                HourRangeTime
    1   John    2017-01-29T16:00:00Z    2017-01-29T16:12:00Z   16:00

In the example the startdate and the enddate is in the range between 16:00 and 16:12 is in the range 16:00

The result it should be like this:

ID  Name    StartDate               EndDate                   HourRangeTime
1   John    2017-01-29T16:00:00Z    2017-01-29T16:12:00Z      16:00
2   Mario   2017-01-29T16:17:00Z    2017-01-29T16:29:00Z      16:15
3   Kate    2017-01-15T10:35:00Z    2017-01-15T10:40:00Z      10:30
4   Maria   2017-01-15T10:17:00Z    2017-01-15T10:27:00Z      10:15
5   Oliver  2017-01-15T13:46:00Z    2017-01-29T14:00:00Z      13:45

How can I fill the column HourRangeTime, take dates and see what range does it belong to?

date sql-server sql
4 Answers
Related