CODEWITHSUNDEEP
c#sql-serverssis
user3063291
user3063291

Reputation: 23

Selecting data between 2 DateTime values

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 .

  1. It takes 2 datetime values fills it in 2 variable .
  2. then these 2 variables are used to create sql statement which selects data between the 2 variable range .
  3. 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 :)

Upvotes: 2

Views: 301

Answers (2)

billinkc
billinkc

Reputation: 61221

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();

Upvotes: 4

Johnny Bones
Johnny Bones

Reputation: 8402

Turning this into an answer.

Uncomment the word "WHERE". That's where your error is.

This block of code:

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() + "')) ";

should look like this:

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() + "')) ";

Upvotes: 1

Related Questions