none
Passing a DateTime to a SQL parameter

    Question

  • Hello, 

    I have in my SQL Server a table with a column of a 'datetime' type.

    I am trying to get information using stored procedure, in my c# application.

    This is what I try to pass:

     

    _sql_command = new SqlCommand("my_procedure", _sql_connection);
    
    _sql_command.CommandType = CommandType.StoredProcedure;
    
    
    SqlParameter param_DATE = _sql_command.Parameters.Add("@_date", SqlDbType.DateTime);
    
    param_DATE.Value = dt; //dt is a DateTime type defined earlier in c#
    
    DataTable data_table = new DataTable();
    
    sql_adapter = new SqlDataAdapter(_sql_command);
    
    sql_adapter.Fill(data_table);
    My stored procedure works when I test it in the Sql Management Studio.
    when I try to run the query from the code I get an empty table.

    • Moved by Bob BeaucheminMVP Thursday, July 01, 2010 9:30 PM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Thursday, July 01, 2010 9:33 AM

Answers

  • Date format is not the issue here for sure, since parameters take care of any format differences. If code does not generate any exception during runtime, I would recommend checking two things

    1. Make sure that time portion of DataTime value passed from the application does not filter out records. Keep in mind that when stored procedure is executed with provided DataTime value, it will use time portion as well and it may return different result, depending on what was passed. If you want to return all the records for specific date, make sure that time portion is set to 12AM or 12PM, depending on logic you applied inside of stored procedure

    2. You could run SQL Profiler and check what is actually passed to the server from the client. Just make sure that parameter value is the same as you expect

     


    Val Mazur (MVP) http://www.xporttools.net
    Friday, July 02, 2010 10:24 AM
    Moderator
  • Hi Spivakov83,

    Please check the value of dt and the values of the date in database (including the second and millisecond value), to see whether the database table has the exact records that meet the search criteria. By the way, the following code works for me :

    string strConn = "Data Source = .\\SQLExpress; Initial Catalog = Northwind; Integrated Security = True";
    SqlConnection _sql_connection = new SqlConnection(strConn);
    
    SqlCommand _sql_command = new SqlCommand("GetDetenions", _sql_connection);
    _sql_command.CommandType = CommandType.StoredProcedure;
    
    SqlParameter param_DATE = _sql_command.Parameters.Add("@_date", SqlDbType.DateTime);
    
    DateTime dt = new DateTime(2010,10,11);
    param_DATE.Value = dt; //dt is a DateTime type defined earlier in c#
    
    DataTable data_table = new DataTable();
    
    SqlDataAdapter sql_adapter = new SqlDataAdapter(_sql_command);
    
    sql_adapter.Fill(data_table);
    
    this.dataGridView1.DataSource = data_table;
    

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 02, 2010 10:33 AM
    Moderator

All replies

  • Hello,

    Is @_date really the parameter name you used in your CREATE PROC definition? The parameter name must be the same to get it working.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, July 01, 2010 10:18 AM
  • Yes of course.

    I've been thinking, maybe the reason is the different date formats of Dot.Net and TSQL datetime?

    Thursday, July 01, 2010 10:41 AM
  • No, .NET sets parameter in correct format.
    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, July 01, 2010 10:51 AM
  • Date format is not the issue here for sure, since parameters take care of any format differences. If code does not generate any exception during runtime, I would recommend checking two things

    1. Make sure that time portion of DataTime value passed from the application does not filter out records. Keep in mind that when stored procedure is executed with provided DataTime value, it will use time portion as well and it may return different result, depending on what was passed. If you want to return all the records for specific date, make sure that time portion is set to 12AM or 12PM, depending on logic you applied inside of stored procedure

    2. You could run SQL Profiler and check what is actually passed to the server from the client. Just make sure that parameter value is the same as you expect

     


    Val Mazur (MVP) http://www.xporttools.net
    Friday, July 02, 2010 10:24 AM
    Moderator
  • Hi Spivakov83,

    Please check the value of dt and the values of the date in database (including the second and millisecond value), to see whether the database table has the exact records that meet the search criteria. By the way, the following code works for me :

    string strConn = "Data Source = .\\SQLExpress; Initial Catalog = Northwind; Integrated Security = True";
    SqlConnection _sql_connection = new SqlConnection(strConn);
    
    SqlCommand _sql_command = new SqlCommand("GetDetenions", _sql_connection);
    _sql_command.CommandType = CommandType.StoredProcedure;
    
    SqlParameter param_DATE = _sql_command.Parameters.Add("@_date", SqlDbType.DateTime);
    
    DateTime dt = new DateTime(2010,10,11);
    param_DATE.Value = dt; //dt is a DateTime type defined earlier in c#
    
    DataTable data_table = new DataTable();
    
    SqlDataAdapter sql_adapter = new SqlDataAdapter(_sql_command);
    
    sql_adapter.Fill(data_table);
    
    this.dataGridView1.DataSource = data_table;
    

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 02, 2010 10:33 AM
    Moderator
  • Alex,

    is "(2010,10,11)" in a yyyy-mm-dd format or yyyy-dd-mm?

    Friday, July 02, 2010 10:41 AM
  • Hi Spivakov83,

    It is a "yyyy-mm-dd" format.

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 02, 2010 10:45 AM
    Moderator
  • Spivakov83,

    Do not focus on DateTime format, since if you pass DataTime parameters, they are not passed in specific format, but as a binary number of milliseconds since (I do not remember which one) some data and formatting does not make any difference in this case. Try to check how your logic is built inside of SP around time portion of filtering.

     


    Val Mazur (MVP) http://www.xporttools.net
    • Proposed as answer by navyjax2 Monday, November 28, 2011 2:27 PM
    Friday, July 02, 2010 10:53 AM
    Moderator