compare DateTime to smallDateTime?

[BonaDrag]

I want to compare a DateTime date & time in a GridView to a smallDateTime date & time in a database table. I'm not sure what goes wrong, but I get the error message "incorrect syntax near 'whatever time'" every time. An example: A DateTime datumStartTid could be '2010-07-25 19:00' to which I add ':00' and the smallDateTime in the database table looks like this '2010-07-25 19:00:00'.
What did I do wrong? Maybe the error is somewhere else but after trying different approaches this is what I've come down to. Some of my code below with an error message after trying to compare the example above.

DateTime datumStartTid = Convert.ToDateTime(datum + " " + starttid + ":00");

        SqlConnection .........

        string sql = "DELETE FROM Tillfalle WHERE Tillfalle.datumStartTid = " + datumStartTid;
        SqlCommand comm = new SqlCommand(sql, conn);
        SqlDataReader dr = comm.ExecuteReader(); //Incorrect syntax near '19'.
        dr.Close();

[Geekitygeek]

Try debugging your code and checking the value of your DateTime before you try converting it. A DateTime holds a value like "03/08/2010 10:35:46" which is what you say you need.
Also, you would want to use DateTime.Parse if you wanted to build the date as a string and convert it:

string Date = "03/08/2010";
    string Time = "10:35";
    DateTime dt = DateTime.Parse(Date + " " + Time + ":00");

[BonaDrag]

Try debugging your code and checking the value of your DateTime before you try converting it. A DateTime holds a value like "03/08/2010 10:35:46" which is what you say you need.
Also, you would want to use DateTime.Parse if you wanted to build the date as a string and convert it:

string Date = "03/08/2010";
    string Time = "10:35";
    DateTime dt = DateTime.Parse(Date + " " + Time + ":00");

I don't really get how I could use a value like "03/08/2010". My database table demands that the values look like the example in my first post (I've entered test data manually), and that's also what they look like when I debug the program and check the values before converting them. Or maybe I just misunderstand what you mean :)

[Geekitygeek]

The format of the datetime is region specific. Here in the UK we generally display dates in the format i used.
The format isnt really important, DateTime.Parse can recognise 'standard' date formats and you can use an IFormatProvider to specify custom formats if necesary.
I ran the same code with your date format and it works fine without any changes to region settings:

string Date = "2010-07-25";
            string Time = "10:35";
            DateTime dt = DateTime.Parse(Date + " " + Time + ":00");

On further reading i just found that Convert.ToDateTime uses DateTime.Parse anyway so its all a moot point lol.

I think there may be some confusion, did you try debugging your code as i suggested to see what values you are comparing?
Datetime stores the date including milliseconds in format "2010-08-03 12:00:00" (in your region anyway :p) whereas a smallDateTime in sql server only stores time including minutes: "2010-08-03 12:00". Are you sure you're not adding extra ":00" where you should be removing them?

[BonaDrag]

I think there may be some confusion, did you try debugging your code as i suggested to see what values you are comparing?
Datetime stores the date including milliseconds in format "2010-08-03 12:00:00" (in your region anyway :p) whereas a smallDateTime in sql server only stores time including minutes: "2010-08-03 12:00". Are you sure you're not adding extra ":00" where you should be removing them?

I did debug, and as far as I can see the values that comes out are in format "2010-08-03 12:00" (from my gridview, this is where I add ":00") and "2010-08-03 12:00:00" (from the database table). When I entered my test data manually I entered in format "2010-08-03 12:00" and the sql management server adds an extra ":00" at the end of every value (even when I try to remove them). That's why I add them in my code. I guess I have missed something about how this is supposed to work, I'm still a beginner...

I've even tried making a new database table where my date & time are text (string), just to see if the comparison would work better but I still get the exact same error message.

[Geekitygeek]

Perhaps the problem isnt the value your passing, but how you are passing it :/
Try using a parameter instead:

string sql = "DELETE FROM Tillfalle WHERE Tillfalle.datumStartTid = @StartDate";
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.Add("@StartDate", SqlDbType.SmallDateTime);
comm.Parameters["@StartDate"].Value = datumStartTid;
SqlDataReader dr = comm.ExecuteReader();

[BonaDrag]

Perhaps the problem isnt the value your passing, but how you are passing it :/
Try using a parameter instead:

string sql = "DELETE FROM Tillfalle WHERE Tillfalle.datumStartTid = @StartDate";
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.Add("@StartDate", SqlDbType.SmallDateTime);
comm.Parameters["@StartDate"].Value = datumStartTid;
SqlDataReader dr = comm.ExecuteReader();

That worked! Thank you so much! I'm sure I'll be back soon with new threads about new problems...:)

[Geekitygeek]

No Worries, sorry it took me so long :p I was so busy trying to get to the bottom of your smallDate -> DateTime formatting that i completely overlooked the obvious issue.
Glad its all working now :)

[BonaDrag]

No Worries, sorry it took me so long :p I was so busy trying to get to the bottom of your smallDate -> DateTime formatting that i completely overlooked the obvious issue.
Glad its all working now :)

Yo have absolutely no reason to apologize!