Just a bit of an exercise to show what can be done, and some of the possible issues if the date format isn't always the same
declare @tbl table
(txtstr varchar(50)
)
insert into @tbl
select ' 02-28-2017, 04:03 PM asb' as str
-- 12345678901234567890
union
select ' 12-28-2017, 04:03 PM asb' as str
-- 12345678901234567890
union
select '28-12-2017, 04:03 PM asb' as str
-- -- 12345678901234567890
union
select ' 2-28-2017, 04:03 PM asb' as str
-- -- 12345678901234567890
union
select ' 02/1/2017, 4:03 PM asb' as str
-- -- 12345678901234567890
select txtstr
, dates.*
, parts.*
, pats.*
from @tbl
--where str like '%-%-%,%:%[aApP][mM]%'
--outer apply (select replace(replace(txtstr, '-', '/'), ',' , ' ') as newtxtstr) newstr
outer apply (select 'patindexes' as names
, patindex('% [aApP][mM]%', txtstr) + 1 as ampmindex
, patindex('%,%:%[aApP][mM]%', txtstr) + 2 as timeindex
, patindex('%[0-9][0-9][-/]%[0-9][-/][0-9][0-9]%,%:%[aApP][mM]%', txtstr) as dateindex1 -- date with leading zero
, patindex('%[0-9][-/]%[0-9_][-/][0-9][0-9]%,%:%[aApP][mM]%', txtstr) as dateindex2 -- date without leading zero
) as pats
outer apply (select 'parts' as names
,rtrim(replace(replace(substring(txtstr, case when dateindex1 = 0 then dateindex2 else dateindex1 end, pats.timeindex - 2), '-', '/'), ',' , ' ')) as fulldate
,substring(txtstr, pats.timeindex, pats.ampmindex - pats.timeindex + 2) as fulltime
) as parts
outer apply (select try_convert(date, parts.fulldate, 101) as month_day_year_date
, try_convert(date, parts.fulldate, 103) as day_month_year_date
, convert(time(0), parts.fulltime) as time
) as dates
output
txtstr month_day_year_date day_month_year_date time names fulldate fulltime names ampmindex timeindex dateindex1 dateindex2
02/1/2017, 4:03 PM asb 2017-02-01 2017-01-02 16:03:00 parts 02/1/2017 4:03 PM patindexes 18 13 2 3
02-28-2017, 04:03 PM asb 2017-02-28 NULL 16:03:00 parts 02/28/2017 04:03 PM patindexes 20 14 2 3
12-28-2017, 04:03 PM asb 2017-12-28 NULL 16:03:00 parts 12/28/2017 04:03 PM patindexes 20 14 2 3
2-28-2017, 04:03 PM asb 2017-02-28 NULL 16:03:00 parts 2/28/2017 04:03 PM patindexes 19 13 0 2
28-12-2017, 04:03 PM asb NULL 2017-12-28 16:03:00 parts 28/12/2017 04:03 PM patindexes 19 13 1 2
The issue with the above is that depending on the date format we either do not have a valid date on the try_format, or we get 2 different dates for the same input
As for your particular needs the following would be the basis for your output
SELECT hourpart.header
, Count (*)
FROM @tbl
outer apply (select patindex('% [aApP][mM]%', txtstr) + 1 as ampmindex
, patindex('%,%:%[aApP][mM]%', txtstr) + 2 as timeindex
) as pats
outer apply (select datepart(hour, convert(time(0), substring(txtstr, pats.timeindex, pats.ampmindex - pats.timeindex + 2))) as timehour
) as times
outer apply (select *
from (values (0, '0-1 AM') , (1, '1-2 AM') , (2, '2-3 AM') , (3, '3-4 AM') , (4, '4-5 AM') , (5, '5-6 AM')
, (6, '6-7 AM') , (7, '7-8 AM') , (8, '8-9 AM') , (9, '9-10 AM') , (10, '10-11 AM'), (11, '11-12 AM')
, (12, '0-1 PM'), (13, '1-2 PM'), (14, '2-3 PM'), (15, '3-4 PM') , (16, '4-5 PM') , (17, '5-6 PM')
, (18, '6-7 PM'), (19, '7-8 PM'), (20, '8-9 PM'), (21, '9-10 PM'), (22, '10-11 PM'), (23, '11-12 PM')
) t(workhour, header)
where times.timehour = t.workhour
) hourpart
--Where patindex('%[0-9][-/]%[0-9_][-/][0-9][0-9]%,%:%[aApP][mM]%', txtstr) > 0
where patindex('%,%:%[aApP][mM]%', txtstr) > 0
group by hourpart.header
Note the where clauses above - depending on your data you only need one of them, but if there is a possibility that the second patindex picks invalid records, then use the first instead