locked
DateTime SQL Statement Problem RRS feed

  • Question

  •  

    Hi,

     

    I am using these SQL statements to extract data from MS SQL 2005 Express but I am not getting any records maybe because the appointment_start field is datetime field. I tried Date(appointment_start) but i got Syntax error.

     

    "select * from appointments where appointment_start between '" + DateTime.Now.ToShortDateString() + "' and '" + DateTime.Now.ToShortDateString() + "' order by appointment_start, appointment_file_no"

     

    "select * from appointments where appointment_start = '" + DateTime.Now.ToShortDateString() + "' order by appointment_start, appointment_file_no"

     

    Wednesday, April 5, 2006 6:28 PM

Answers

  • Using parametrized queries solves all your problems....
    Now, you just add the date as a string, in the format that you're using to represents dates.   Sql server may not recognize this as a valid date.
    So, to avoid this, you can use parametrized queries:

    string query = "select * from appointments where start_date = @startdate";
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = query;
    cmd.Parameters.Add ("@startdate", SqlDbType.DateTime);
    cmd.Parameters["@startdate"].Value = DateTime.Now
    etc....

    For more info:
    http://fgheysels.blogspot.com/2005/12/avoiding-sql-injection-and-date.html



     

    Wednesday, April 5, 2006 7:42 PM

All replies

  • Using parametrized queries solves all your problems....
    Now, you just add the date as a string, in the format that you're using to represents dates.   Sql server may not recognize this as a valid date.
    So, to avoid this, you can use parametrized queries:

    string query = "select * from appointments where start_date = @startdate";
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = query;
    cmd.Parameters.Add ("@startdate", SqlDbType.DateTime);
    cmd.Parameters["@startdate"].Value = DateTime.Now
    etc....

    For more info:
    http://fgheysels.blogspot.com/2005/12/avoiding-sql-injection-and-date.html



     

    Wednesday, April 5, 2006 7:42 PM
  •  

    but my routine is very simple and i don't want to use the Parameters. This is my code and i am just passing the SQL statement.

     

      void get_appointments(string strSQL)
      {
       MessageBox.Show(strSQL);

       DataSet ds_appointment = new DataSet("appointments");
       SqlCommandBuilder cmdBuilder;

       ///instantiate SqlDataAdapter to create DataSet
       SQLadapter = new SqlDataAdapter(strSQL, SQLconn);
     
       /// the following is for creating automatic command builder
       cmdBuilder = new SqlCommandBuilder(SQLadapter);

       ///fill the dataset
       SQLadapter.Fill(ds_appointment, "appointments");
     
       ///set the dataset as a datasource for windows datagrid
       dataGrid_Appointment.SetDataBinding(ds_appointment, "appointments");
      }

     

    and just a reminder again.. the appointment_start is a datetime field and i just need to extract the date and show all records in a specefic date or between two dates.

     

    Wednesday, April 5, 2006 9:15 PM
  • Why don't you want to use parameters ?
    What if the date is in another format ? What if the date-separator is - instead of / ?
    How are you going to prevent sql injection ?
    How are you going to insert strings that contains quotes ?

    Parameters help you in all those issues, they take care of all that without any further effort on your part.

    Just a reminder: use parameters, read the article for which I've given you the link.
    Thursday, April 6, 2006 7:13 AM
  • You should put date values in apostrofs like string values.
    So you code be like :

    "select * from table where datecolumn between '" + datefrom.ToShortDateString() + "' and '" + dateto.ToShortDateString() + "'"

    Using string conversion metods of datetime type is not smart. That's why it's smarter to use parameters because no conversion is needed and if you really need to use string for select statement than convert datetime value to format yyyyMMdd HH:mm:ss because this is sql server system datetime format and will work always no mater the format of current windows user's setting.

     

    Thursday, April 6, 2006 7:20 AM
  • ok.. let me explain something..

     

    in appointment_start I ave the following two records

    4/6/2006 11:00:00 AM

    4/6/2006 02:15:00 PM

     

    when i use the following SQL statement in SQL Server Query I get the result.

     

    select * from appointments where appointment_start = '4/6/2006 11:00:00 AM'

    select * from appointments where appointment_start = '4/6/2006 02:15:00 PM'

     

    but i want to get a statement which should list all in 4/6/2006 inrespect of the time.

     

    In SQL Server Query, I tried:

     

    select * from appointments where appointment_start = '4/6/2006'

     

    but it's returning no records..

    Thursday, April 6, 2006 3:36 PM
  • You can do it in many ways.
    For example:

    select * from appointments where appointment_start between '20060604 00:00:00' and '20060604 23:59:59'

    or

    select * from appointments where Year(appointment_start) = 2006 and Month(appointment_start) = 6 and Day(appointment_start) = 4

     

    Thursday, April 6, 2006 3:49 PM
  • use datediff...
    Friday, April 7, 2006 9:42 AM