DateTime conversion problems on sync

All replies

• 

  

  0

  Sign in to vote

   

  Hello,

   

  What is the collation / language setting on the SQL server? This message you are getting, I suspect, is coming from the SQL Server.

   

  As you might already know, when you are using SQL Server in different language collation, the data (specifically, the datetime data in this case) needs to be in the same format as the collation language on the SQL Server. Otherwise, the query will complain saying "incorrect date time format".

   

  In order to handle this problem, you can use CONVERT(datetime,[your date],[your date style]) to convert your datetime data into the format that is compatible with your SQL Server collation.

   

  For more information, please take a look at the following article:

  http://msdn.microsoft.com/en-us/library/aa226054.aspx

   

  Hope this helps,

   

  Patrick

   

   

  Wednesday, September 17, 2008 6:35 PM
• 

  

  0

  Sign in to vote

  Hello Patrick,

   

  thanks for your reply.

   

  The SQL server is 2008 and set to german, dateformat 'dmy'

   

  select getdate() brings "2008-09-18 09:06:55.260"

   

  On the client I have SQL Compact 3.5 SP1, in the properties window (when opening with SQL Management Studio) it says it is set to "German-Austria".

   

  select getdate() brings the exactly same format as on the server.

   

  In my application I do inserts on the Compact SQL, inserting DateTime columns with getdate().

   

  In the Sync Adapter insert and update command scripts I add the values like

   

  insert into [table] ([datecolumn]) values (@datevalue)

   

  and define the parameter like

  insertCommand.Parameters.Add("@datevalue", SqlDbType.DateTime);

   

  When running the sync a conversion error like I stated in my first post is risen.

   

   

  Is there anything I did not see or configure correctly in my setting?

   

  Or would you suggest to add those CAST and CONVERT functions to all tatements that deal with datetimes?

  So e.g. never directly insert GETDATE() but do something like

  Hello Patrick,

   

  thanks for your reply.

   

  The SQL server is 2008 and set to german, dateformat 'dmy'

   

  select getdate() brings "2008-09-18 09:06:55.260"

   

  On the client I have SQL Compact 3.5 SP1, in the properties window (when opening with SQL Management Studio) it says it is set to "German-Austria".

   

  select getdate() brings the exactly same format as on the server.

   

   

   

  In my application I do inserts on the Compact SQL, inserting DateTime columns with getdate().

   

  In the Sync Adapter insert and update command scripts I add the values like

   

  insert into [table] ([datecolumn]) values (@datevalue)

   

  and define the parameter like

  insertCommand.Parameters.Add("@datevalue", SqlDbType.DateTime);

   

  When running the sync a conversion error like I stated in my first post is risen.

   

   

  Is there anything I did not see or configure correctly in my setting?

   

  Or would you suggest to add those CAST and CONVERT functions to all tatements that deal with datetimes?

   

  So e.g. never directly insert GETDATE() on the client DB but do something like

   

  select CONVERT(char(8), GETDATE(), 112)

   

  and in the sync script

   

  insert into [table] ([datecolumn]) values (CAST(CONVERT(char(8), @datevaluie, 112) AS datetime))

   

  I would much appreciate any suggestions or "datetime treatment" best practices.

   

  Andreas

  Thursday, September 18, 2008 7:27 AM
• 

  

  0

  Sign in to vote

   

  Hello Andreas,

   

  My suggestion would be to convert your datetime data within the insert command that you pass to the SyncAdaptor.

   

  First, try SQL Convert directly and make sure your conversion works correctly (i.e. know exactly what format you are passing in).

   

  select (convert(datetime, 'your sample date', your datetime style))

   

  Then, embed this Convert command into your query.

   

  For example,

   

  insert into [table] ([column name]) values (convert(datetime, @datevalue, datetime style))

   

  Hope this helps,

   

  Patrick

  • Proposed as answer by Patrick S. Lee Wednesday, February 11, 2009 2:14 AM
  • Marked as answer by forstingera Wednesday, March 18, 2009 8:01 AM

  Saturday, September 20, 2008 8:03 PM