Datetime variable doesn't work

  • Hello,

    Can you please help and let me know why the next select with @start_date and @end_date doesn't work, i meant the select keep running for ever unless i stop it?

    Beforehand thank you

    declare @start_date datetime

    declare @end_date datetime

    set @start_date='2013-12-30 00:00:01.000'

    set @end_date='2013-12-30 23:59:59.000'

    SELECT si.site_name, si.site_addr1, si.site_addr2, si.city_name, si.zip_code, si.county_name, ai.alarminc_no,sy.cs_no,st.descr,ai.alarm_local_date,a.agency_name, ai.agency_ref_id, ai.alarm_zone_id,

    ai.dispatch_local_date, ev.descr as name, u.descr as tipo

    FROM alarm_incident ai INNER JOIN

    system sy ON ai.system_no = sy.system_no INNER JOIN

    site si ON sy.site_no = si.site_no INNER JOIN

    agency a ON ai.agency_no = a.agency_no INNER JOIN

    udf u ON si.udf1 = u.udf_id INNER JOIN

    event_history eh ON ai.alarminc_no = eh.alarminc_no INNER JOIN

    event ev ON eh.event_id = ev.event_id INNER JOIN

    service_type st ON st.servtype_id=ai.servtype_id

    WHERE (ai.full_clear_opactdisp_id='DP') AND (ai.alarm_local_date between @start_date and @end_date) AND (eh.full_clear_flag = 'Y')

    Same select with date not like a variable works fine and quickly

    SELECT si.site_name, si.site_addr1, si.site_addr2, si.city_name, si.zip_code, si.county_name, ai.alarminc_no,sy.cs_no,st.descr,ai.alarm_local_date,a.agency_name, ai.agency_ref_id, ai.alarm_zone_id,

    ai.dispatch_local_date, ev.descr, udf.descr as tipo

    FROM alarm_incident ai INNER JOIN

    system sy ON ai.system_no = sy.system_no INNER JOIN

    site si ON sy.site_no = si.site_no INNER JOIN

    agency a ON ai.agency_no = a.agency_no INNER JOIN

    udf ON si.udf1 = udf.udf_id INNER JOIN

    event_history eh ON ai.alarminc_no = eh.alarminc_no INNER JOIN

    event ev ON eh.event_id = ev.event_id INNER JOIN

    service_type st ON st.servtype_id=ai.servtype_id

    WHERE (ai.full_clear_opactdisp_id='DP') AND (ai.alarm_local_date between '2013-12-30 00:00:01.000' and '2013-12-30 23:59:59.000') AND (eh.full_clear_flag = 'Y')

  • Parameter sniffing, or more accurately lack thereof. http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/

    Try this, and note the slight changes I made to the dates and date comparison as well, as the original version could miss rows with the datetime very close to midnight

    DECLARE @start_date DATETIME

    DECLARE @end_date DATETIME

    SET @start_date = '2013-12-30 00:00:00'

    SET @end_date = '2013-12-31 00:00:00'

    SELECT si.site_name ,

    si.site_addr1 ,

    si.site_addr2 ,

    si.city_name ,

    si.zip_code ,

    si.county_name ,

    ai.alarminc_no ,

    sy.cs_no ,

    st.descr ,

    ai.alarm_local_date ,

    a.agency_name ,

    ai.agency_ref_id ,

    ai.alarm_zone_id ,

    ai.dispatch_local_date ,

    ev.descr AS name ,

    u.descr AS tipo

    FROM alarm_incident ai

    INNER JOIN system sy ON ai.system_no = sy.system_no

    INNER JOIN site si ON sy.site_no = si.site_no

    INNER JOIN agency a ON ai.agency_no = a.agency_no

    INNER JOIN udf u ON si.udf1 = u.udf_id

    INNER JOIN event_history eh ON ai.alarminc_no = eh.alarminc_no

    INNER JOIN event ev ON eh.event_id = ev.event_id

    INNER JOIN service_type st ON st.servtype_id = ai.servtype_id

    WHERE ( ai.full_clear_opactdisp_id = 'DP' )

    AND ( ai.alarm_local_date >= @start_date AND ai.alarm_local_date < @end_date )

    AND ( eh.full_clear_flag = 'Y' )

    OPTION (RECOMPILE)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail,

    Thanks a lot for your reply and solution, it works fine now with OPTION (RECOMPILE)

    Have a happy next new year 2014 🙂

    Regards

    Claudio

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply