CODEWITHSUNDEEP
c#sqldatetimesql-server-ce
meds
meds

Reputation: 22916

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...

Upvotes: 6

Views: 17627

Answers (5)

Rafael
Rafael

Reputation: 974

Man, you do not need to convert string to DateTime.

Use a instance of a new DateTime and pass the date as parameter. Like this:

using (var ctx = new DBPreparoEntities())
{
    var _client = from p in ctx.Client
                     select new Client
                     {
                         data = new DateTime(2016,08,17),
                         dateconf = null,
                         scod_cli = p.Rua,
                         valorini = 7214.62m,
                     };
    return client.ToList();
}

don't use:

... data = DateTime.Parse("2016/12/10") // or other type convertions.

Upvotes: 1

Phil
Phil

Reputation: 1973

sorry this is in vb.net, but this is a method i use to convert from a CE date/time format:

Public Shared Function ConvertSqlDateTimeFormat(ByVal s As String) As String
    Dim theDate As New Text.StringBuilder
    Dim sTemp As String = ""
    Dim iIndex As Integer

    If s.Length > 8 Then
        'first we do the time
        iIndex = s.IndexOf(" ", System.StringComparison.OrdinalIgnoreCase)
        If iIndex > 0 Then
            sTemp = s.Substring(iIndex).Trim
            iIndex = sTemp.IndexOf(".", System.StringComparison.OrdinalIgnoreCase)
            If iIndex > 0 Then
                sTemp = sTemp.Substring(0, iIndex)
            End If
        End If

        'next the date
        theDate.Append(s.Substring(4, 2))
        theDate.Append("/")
        theDate.Append(s.Substring(6, 2))
        theDate.Append("/")
        theDate.Append(s.Substring(0, 4))
        theDate.Append(" ")
        theDate.Append(sTemp)
    End If
    Return theDate.ToString
End Function

Upvotes: 0

brahmam chary
brahmam chary

Reputation: 1

private void button1_Click(object sender, EventArgs e)
{
    var cnn1 ="";//connection string 
    SqlCeConnection cnn = new SqlCeConnection(cnn1);   
    datetime dt4 = DateTime.Today.Date.ToString("yyyyMMdd").trim();//format 
    var qry ="insert into tbl_test(User_Id, DateOfJoin)values (11,'" + dt4 + "')";
   
    cmd = new SqlCeCommand(qry, cnn);
   
    try
    {
        dr = cmd.ExecuteReader();
    }
    catch (Exception ex)
    {
        string sr = ex.Message;
        throw;
    }
}

Above code worked for me.

1

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

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 parameter 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.

Upvotes: 18

Jake1164
Jake1164

Reputation: 12349

Try the following format:

DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")

Upvotes: 6

Related Questions