Geek Answers Handbook

Correct DateTime format in SQL Server CE?

I have a C # DateTime class and I want to know how I need to format it in a SQL Server CE query in order to insert it into the database, I was hoping that both the date and the date are inserted. try the options. I get invalid format exceptions.

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

The way I'm trying to make an insert looks like this:

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

Obviously, hh:mm:ss does not work; if it is not, dd/MM/yyyy succeeds in the request.

I tried several formats, including what I found on google, but so far no one has worked ...

+4
source share
4 answers

If you are worried about getting the format correctly, something is already seriously wrong. To work correctly with datetime values ​​in any database, you need to do two things: not only sqlce:

  • Make sure that the column is using the datetime type (not a text type like varchar).
  • Make sure you use the datetime parameters in a parameterized query, and not in string concatenation.

If you do, formatting will not be performed 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 use string manipulations to replace values ​​in sql queries. This is a huge no-no.

+12
source

Try the following format:

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

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

Use an instance of the new DateTime and pass the date as a 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(); }

do not use:

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

Sorry this is in vb.net, but this is the method I use to convert from 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
0
source

More articles:


All Articles