Selecting data between 2 DateTime values

Question

Here is what is in SSIS script task in visual c#

public void Main()
{
    // TODO: Add your code here
    int Var_Flag = (int)Dts.Variables["Var_Flag"].Value;                        // 1 for manual run, 0 for incremental since last run
    DateTime Start_Date = (DateTime)Dts.Variables["Var_StartDate"].Value;       // Start DateTime of the previous run. Start at this date if run is set to manual
    DateTime End_Date = (DateTime)Dts.Variables["Var_EndDate"].Value;           // End DateTime of the previous run. End at this date if run is set to manual

    String SQL = "";

    // If incremental run, our current run needs to start at the end date from the previous run and include everything up to today's date
    // Format the SQL string

    SQL = "SELECT * " + "   from rz_ibsv.dbo.INTGR_INSTLD_BASE_SRVC_CNTRCT_F"+
    // "WHERE " +
    //"(convert(datetime2,UPD_GMT_TS) >=CONVERT(DATETIME,'" + Start_Date + "',131) AND convert(datetime2,UPD_GMT_TS) < CONVERT(DATETIME,'" + End_Date + "',131)) ";
    //"(convert(UPD_GMT_TS >=cast('" + Start_Date + "',datetime) AND UPD_GMT_TS < cast('" + End_Date + "',datetime) "; 
    //"UPD_GMT_TS BETWEEN '" + Start_Date + "' and '" + End_Date + "'";
  "(UPD_GMT_TS >=CONVERT(datetime,'" + Start_Date.ToString() + "') AND UPD_GMT_TS < CONVERT(datetime,'" + End_Date.ToString() + "')) ";

    // Return the SQL string to the global variable

    Dts.Variables["Intervention_Parts_Qry"].Value = SQL;
    Dts.TaskResult = (int)ScriptResults.Success;
}

What this code does is .

It takes 2 datetime values fills it in 2 variable .
then these 2 variables are used to create sql statement which selects data between the 2 variable range .
This sql statment is used in execute sql task to select desired output .

I am getting error as :

[OLEDB_SRC- COREIB CONTRACT DATA [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Incorrect syntax near '>'.".

Need help resolving this issue :)

billinkc billinkc · Accepted Answer · 2013-12-03T21:52:08

The trick in these situations is two-fold. The first is to not use string building for SQL Statements. That's what Parameters are for. As a bonus, it helps guard against SQL Injection attacks.

The second thing to do when you are encountering oddities is to display the command. Assuming you have a faithful representation of the code your SQL variable is going to evaluate to

SELECT * from rz_ibsv.dbo.INTGR_INSTLD_BASE_SRVC_CNTRCT_FWHERE (UPD_GMT_TS...

You need a space between your table/view and the WHERE clause.

Thus

SQL = "SELECT * " 
    + " FROM rz_ibsv.dbo.INTGR_INSTLD_BASE_SRVC_CNTRCT_F AS F 
    + "WHERE " ...

A better approach using SqlCommand would be

    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    command.CommandText = "SELECT F.* FROM rz_ibsv.dbo.INTGR_INSTLD_BASE_SRVC_CNTRCT_F AS F WHERE F.UPD_GMT_TS >= @StartDate AND F.UPD_GMT_TS < @EndDate ";
    command.CommandType = CommandType.Text;

    SqlParameter parameter = new SqlParameter();
    parameter.ParameterName = "StartDate";
    parameter.SqlDbType = SqlDbType.DateTime;
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = Start_Date;

    command.Parameters.Add(parameter);

    parameter = new SqlParameter();
    parameter.ParameterName = "EndDate";
    parameter.SqlDbType = SqlDbType.DateTime;
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = End_Date;

    command.Parameters.Add(parameter);

    connection.Open();
    SqlDataReader reader = command.ExecuteReader();

Selecting data between 2 DateTime values

2 Answers