how do start time and end time without date sql server

Posted by Cpatil1000 under ASP.NET on 4/27/2017 | Points: 10 | Views : 3475 | Status : [Member] | Replies : 1
Hi,

I am trying to solve this sql query. But my sql query is giving wrong count..
in table i have time column which depend upon for query.
Out client office time is :

07:30:00 am to 23:59:59 pm
and night 00:00:00 am to 07:59:59 am


following query how to do this beacause i am getting wrong count..

1)
Select COUNT(*)
From TEMP_VEHICLE_MASTER_2017 V
Inner Join TOLL_MASTER T On V.TOLL_ID = T.TOLL_ID
Inner Join PROJECT_MASTER P On T.PROJECT_ID = P.ID
Inner Join VEHICLE_TYPE_MASTER VTM On VTM.ID = V.TYPE_ID
Inner Join TEMP_VEHICLE_TICKET_DETAIL_2017 VT On V.VEHICLE_ID = VT.TICKET_ID And V.TOLL_ID = VT.TOLL_ID
Where V.TOLL_ID = '102001'
And ((Cast(V.TIME as datetime) >= '07:30:00' And Cast(V.TIME as datetime) <= '23:59:59')
OR (Cast(V.TIME as datetime) >= '00:00:00' And Cast(V.TIME as datetime) <= '07:29:59'))



2) following query how to do this beacause i am getting right count..
but i want add (Cast(V.TIME as datetime) >= '00:00:00' And Cast(V.TIME as datetime) <= '07:29:59'))
so how to do

Select COUNT(*)
From TEMP_VEHICLE_MASTER_2017 V
Inner Join TOLL_MASTER T On V.TOLL_ID = T.TOLL_ID
Inner Join PROJECT_MASTER P On T.PROJECT_ID = P.ID
Inner Join VEHICLE_TYPE_MASTER VTM On VTM.ID = V.TYPE_ID
Inner Join TEMP_VEHICLE_TICKET_DETAIL_2017 VT On V.VEHICLE_ID = VT.TICKET_ID And V.TOLL_ID = VT.TOLL_ID
Where V.TOLL_ID = '102001'
And (Cast(V.TIME as datetime) >= '07:30:00' And Cast(V.TIME as datetime) <= '23:59:59')





Responses

Posted by: Bandi on: 4/27/2017 [Member] [MVP] Platinum | Points: 25

Up
1
Down
declare @tab table (id int identity(1,1),StartTime time, EndTime time)
insert @tab
select '07:30:00 am', '23:59:59 pm' union all
SELECT '00:00:00 am', '07:59:59 am'
Select StartTime, EndTime
From @tab V
Where 1=1
And (
(Cast(V.StartTime as datetime) >= '07:30:00' And Cast(V.EndTime as datetime) <= '23:59:59')
OR (Cast(V.StartTime as datetime) >= '00:00:00' And Cast(V.EndTime as datetime) <= '07:29:59')
)


output:
StartTime EndTime
07:30:00.0000000 23:59:59.0000000


why your first query doesn't return 2nd record is the below condition becomes FALSE
Cast(V.EndTime as datetime) <= '07:29:59')
07:59:59 <= 07:29:59

minutes 59 is NOT LESS THAN 29

This is the reason your first query return incorrect count

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response