SQL Server Query request for start and end period of time


  • Hi all

    I have event table where I need to select records between days. my statements look like

      CONVERT(char(8),Event_Table.Event_time,112) BETWEEN 'request start date' AND 'request end date'

    The Event_time is DateTime format.


    Now everything look fine except from if I need the statement to request the date between as Date_start and Date_End any time it run. The idea is to request a for start and end period of date any time the scrpt run. the script will be run as DOS batch file. 

    Saturday, May 21, 2011 10:08 AM


All replies


    You have to set input values through Script ....


    Try something like this in script.




    SQL = " Select   Event_ID  ,Event_Type  ,Event_Time From   Event_Table Where   CONVERT(char(8),Event_Table.Event_time,112) BETWEEN Convert(DateTime,'" +StartDate + "') AND Convert(DateTime, '"+EndDate +"')"


    If this answer is helpful to you .. Please mark as Answer....
    Saturday, May 21, 2011 10:34 AM
  • You need to use the sqlcmd 

    with some DOS scripting like using the echo  keyword to request input in DOS batch.

    And then u executing the query after getting the input.

    Ali Hamdar ( -
    Saturday, May 21, 2011 10:52 AM
  • In addition to the above, you can set it up like a stored procedure with parameters:

    Your datetime range logic requires fixing for faster execution (not SARGABLE):

    Following WHERE clause predicate is SARGABLE:


    DECLARE @FromDate datetime = '2004-02-01', @ToDate datetime = '2004-02-05'
    SELECT FromDate=@FromDate, ToDate=@ToDate, PurchaseCount=COUNT(*)
    FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader
    WHERE OrderDate >=@FromDate and OrderDate < DATEADD(day,1,@ToDate)
    -- 2004-02-01 00:00:00.000	2004-02-05 00:00:00.000	60


    You can execute the stored procedure several ways:

    1. Set it up as SQL Server agent job with/wihout schedule and supply the input parameters from a control(lookup) table (UPDATE control table before executing the job)

    2. Set it up as an SSRS report, which returns the row count as result

    Controlling jobs programmatically:


    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM

    Saturday, May 21, 2011 1:03 PM