soshiki soshiki - 1 month ago 10
SQL Question

Correct DateTime format in SQL Server CE?

I have a C#

DateTime
class and wanted to know how I need to format it in a SQL Server CE query to insert it into the database, I was hoping to have both the date and time inserted in. Currently when I try variations thereof I get invalid format exceptions.

Current format I'm using is:
dd/MM/yyyy
, was hoping to do something like
dd/MM/yyyy hh:mm:ss
.

The way I'm trying to do the insert is like so:

( ( DateTime )_Value ).ToString( "dd/MM/yyyy hh:mm:ss" )


Obviously
hh:mm:ss
isn't working, if that isn't there
dd/MM/yyyy
executes successfully in the query.

I've tried a few formats including what I've found on google but none have worked so far...

Answer

If you're worried about getting the format right at all, something has already gone seriously wrong. There are two things you need to do to correctly work with datetime values in any database, not just sqlce:

  1. Make sure you're using a datetime type for the column (not a text type like varchar)
  2. Make sure you're using a datetime parameters in a parameterized query, and not string concatenation.

If you do that, there is no formatting involved on your part. At all. Example:

 void SetDate(int recordID, datetime timeStamp)
 {
    string SQL = "UPDATE [sometable] SET someDateTimeColumn= @NewTime WHERE ID= @ID";

    using (var cn = new SqlCeConnection("connection string here"))
    using (var cmd = new SqlCeCommand(SQL, cn))
    {
        cmd.Parameters.Add("@NewTime", SqlDbType.DateTime).Value = timeStamp;
        cmd.Parameters.Add("@ID", SqlDbType.Integer).Value = recordID;

        cn.Open();
        cmd.ExecuteNonQuery();
    }
} 

Never ever ever ever EVER use string manipulation to substitute values into sql queries. It's a huge no-no.

Comments