Thanks for the replies i did figure it out and used my time dimension, below is the code for those wondering:

select distinct t.visit_date,t.car_id,t.car_color,d.hour_of_day_24

from TABLE t

inner join DIMENSION d

on d.string_representation_24 between t.car_start_date and t.car_end_date

the answer to the question above is yes both 0800 get a 1 and 0900 gets 1 regardless if the car was there for an hour. I am trying to capture volume by hour. That is only half of the request the next half will be challenging as well.