none
SQL Server Query request for start and end period of time

    Question

  • Hi all

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

    Select 
      Event_ID
      ,Event_Type
      ,Event_Time
    From 
      Event_Table
    Where 
      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

Answers

All replies

  •  

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

     

    Try something like this in script.

     

    StartDate=InputBox("StartDate")

    EndDate=InputBox("EndDate")

    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 (alihamdar.com - www.ids.com.lb)
    Saturday, May 21, 2011 10:52 AM
  • In addition to the above, you can set it up like a stored procedure with parameters:

    http://www.sqlusa.com/bestpractices2008/stored-procedure-parameters/

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

    http://www.sqlusa.com/bestpractices2008/between-dates/

    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:

    http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/

     


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





    Saturday, May 21, 2011 1:03 PM