how do start time and end time without date sql server

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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')

Reply | Reply with Attachment

Alert Moderator

Responses

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

1	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

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

Latest Posts