DateTime problem in sql compact

Question

I'm using Sql Compact3.5 as my DB with C# .NET In different systems i'm getting the datetime format differently. In an Windows XP it's retrieving the datetime in the format : MM-dd-yyyy HH:mm:ss and in Media center it's retrieving in the format : MM/dd/yyyy hh:m:ss. Is there any way to make the datetime format free from culture or can i set the datetime format in sql compact so let it be any PC it'll use that format only???

Example :

//TimeOfCall is passed as String using the format DateTime.Now.ToString("MM-dd-yyyy HH:mm:ss");
using (SqlCeConnection con = new SqlCeConnection(ConString))
{
    using (SqlCeCommand SqlceCmd = new SqlCeCommand(
         "Insert into myreports(TimeOfCall,Status) values(?,?)", con))
    {
        if (con.State == ConnectionState.Closed)
            con.Open();
        SqlceCmd.Parameters.Add(new SqlCeParameter("@TimeOfCall", strTimeOfCall));
        SqlceCmd.Parameters.Add(new SqlCeParameter("@Status", strStatus));

        int RowsaAffected = SqlceCmd.ExecuteNonQuery();
        con.Close();
        return RowsaAffected;
    }
}

While Rertiving the record the query is used in this way :

//FromTime and ToTime are passeed in the same format as while storing
using (SqlCeConnection con = new SqlCeConnection(ConString))
{
    using (SqlCeDataAdapter SqlceDA = new SqlCeDataAdapter("Select TimeOfCall from myreports where TimeOfCall between '" + strFromTime + "' and '" + strToTime + "' order by TimeOfCall", con))
    {
        if (con.State == ConnectionState.Closed)
            con.Open();
        SqlceDA.Fill(dtReports);
        con.Close();
        return dtReports;
    }
}

I hope it's clear

Answer 1

Ok, from the code, you're basically doing the right thing the wrong way.

The good news is that you're using parameters - that's exactly right - however you don't need actually don't want to convert the date to a string before setting the parameter value.

Simplest should be to change SqlceCmd.Parameters.Add(new SqlCeParameter("@TimeOfCall", strTimeOfCall)); to SqlceCmd.Parameters.AddWithValue("@TimeOfCall", timeOfCall)); where timeOfCall is a DateTime value.

The same applies to the status if that's not natively a string.

If you want to be more explicit about types create the parameter first defining the type and then set it.

For your selection query do the same thing, replace your string concatenation with parameters @fromTime and @toTime and set the parameters directly from the appropriate DateTime values