Finding mins and max time from records with different start time other that 12:00 midnight

I have a series of start and end time records. The problem is to select the min and max time from the series of records.

The following contraint applies. The start of broadcast time is 6:00 am. That is, minimum for start time is 6:00 am and maximum for end time is 5:59 am. So the following is illegal for start and end time, for example, 4:00 am - 6:30 am because it crosses the broadcast start time of 6:00 am.

Start End

10:00 pm -- 2:00 am

6:30 am -- 8:30 am

2:00 am - 3:45 am

11:00 am - 4:00pm

12:00 am - 3:40 am

You might be tempted to used -> Select MIN(Start), Max(End), but that will return 12:00 am - 4:00 pm, which is wrong, because sql server uses 12 midnight at the start time.

Can' t seem to come up with the tsql, please help

In my opinion it's not totally clear exactly what you're looking for.

In the example that you supplied, what results would you expect to be returned by the query? Also, are you storing the data as DATETIME values?

Thanks
Chris

|||Sorry. Since the min begin time is 6:00 am and the maximum end time time is 5:59 am, the expected result should be 6:30 am - 3:45am.|||

Thanks for providing the extra info.

Please could you also clarify what datatype you are using to store the times as this will affect the solution.

Thanks
Chris

|||

job:

I think that I also am not sure exactly what you are trying to do. Maybe a starting point is to substract six hours from your "startTime" to establish a "work day"; something like:

declare @.timeStuff table
( shiftId integer,
startTime datetime,
endTime datetime
)

insert into @.timeStuff values ( 1, '3/4/7 22:00', '3/5/7 2:00' )
insert into @.timeStuff values ( 2, '3/5/7 06:30', '3/5/7 8:30' )
insert into @.timeStuff values ( 3, '3/5/7 02:00', '3/5/7 3:45' )
insert into @.timeStuff values ( 4, '3/5/7 11:00', '3/5/7 16:00')
insert into @.timeStuff values ( 5, '3/6/7 00:00', '3/6/7 3:40')

declare @.offset datetime set @.offset = '06:00:00.000'

select convert (varchar(8), startTime - @.offset, 101) as [workDate ],
left(convert (varchar(8), startTime, 108), 5) as startTime,
left(convert (varchar(8), endTime, 108), 5) as endTime
from @.timestuff
order by startTime

-- workDate startTime endTime
-- -
-- 03/05/20 00:00 03:40
-- 03/04/20 02:00 03:45
-- 03/05/20 06:30 08:30
-- 03/05/20 11:00 16:00
-- 03/04/20 22:00 02:00

|||

If 4 am is not legal how did it get into your db in the first place, going by what you want there shoudl be nothing less than 6.00 am in your Start column. Get the place of entry sorted out and you should fine.

for the bad data u posted above you could still use

Select min(start), Max(End)

from tablename

where

convert(use conversion to convert time to last digits in time in start)>5.59 and

and convert(use conversion to convert time to last digits in time)<

--you can figure out the where condtion

point is it can be done