sql - Custom range field based on Hour and time

Question

I want to create a Custom field like below based on below two fields (Hour and Time). Does anyone know how to do this is SQL?

question from:https://stackoverflow.com/questions/65838892/custom-range-field-based-on-hour-and-time

Answer 1

You do not need the Hour column for this result. The Time values are enough.

Sample data

create table data
(
  TimeValue time(0)
);

insert into data (TimeValue) values
('12:00:00 AM'),
('12:15:00 AM'),
('12:30:00 AM'),
('12:45:00 AM'),
( '1:00:00 AM'),
( '1:15:00 AM'),
( '1:30:00 AM'),
( '1:45:00 AM'),
( '2:00:00 AM'),
( '2:15:00 AM'),
( '2:30:00 AM'),
( '2:45:00 AM'),
( '3:00:00 AM'),
( '3:15:00 AM'),
( '3:30:00 AM'),
( '3:45:00 AM'),
( '4:00:00 AM'),
( '4:15:00 AM'),
( '4:30:00 AM');

Solution

select 'Hour ' + convert(nvarchar(2), datepart(hour, d.TimeValue)) as [Hour],
       convert(nvarchar(11), d.TimeValue, 22) as [Time],
       'Hour ' +
       convert(nvarchar(2), case
                               when datepart(hour, d.TimeValue)-1 < 0 then 0
                               else datepart(hour, d.TimeValue)-1
                             end) + '-' +
       convert(nvarchar(2), datepart(hour, d.TimeValue)) as [Custom]
from data d;

Result

Hour    Time         Custom
------  -----------  --------
Hour 0  12:00:00 AM  Hour 0-0
Hour 0  12:15:00 AM  Hour 0-0
Hour 0  12:30:00 AM  Hour 0-0
Hour 0  12:45:00 AM  Hour 0-0
Hour 1   1:00:00 AM  Hour 0-1
Hour 1   1:15:00 AM  Hour 0-1
Hour 1   1:30:00 AM  Hour 0-1
Hour 1   1:45:00 AM  Hour 0-1
Hour 2   2:00:00 AM  Hour 1-2
Hour 2   2:15:00 AM  Hour 1-2
Hour 2   2:30:00 AM  Hour 1-2
Hour 2   2:45:00 AM  Hour 1-2
Hour 3   3:00:00 AM  Hour 2-3
Hour 3   3:15:00 AM  Hour 2-3
Hour 3   3:30:00 AM  Hour 2-3
Hour 3   3:45:00 AM  Hour 2-3
Hour 4   4:00:00 AM  Hour 3-4
Hour 4   4:15:00 AM  Hour 3-4
Hour 4   4:30:00 AM  Hour 3-4

Fiddle to see things in action.