You must Sign In to post a response.
  • Category: ASP.NET

    How do start time and end time without date sql server

    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 because 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')
  • #769204
    Have you tried with BETWEEN and AND rather than lessthan and greterthan.
    see below snippet

    SELECT *
    FROM [YourTable]
    WHERE [YourDate] BETWEEN '08-15-2011 02:00:00' AND '08-16-2011 02:00:00'

    or

    SELECT * FROM your_table
    WHERE STR_TO_DATE(your_date, '%m-%d-%Y %H.%i.%s') BETWEEN
    DATE_SUB(STR_TO_DATE(DATE_FORMAT(NOW(), '%m-%d-%Y'), '%m-%d-%Y'), INTERVAL 22 HOUR) AND
    DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%m-%d-%Y'), '%m-%d-%Y'), INTERVAL 2 HOUR)

    hope it helps

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


Sign In to post your comments