0

I would like to store DateTime values using the datetime2 type in SQL Server CE 3.5.

If this is possible, how would I change my entity data model? I've tried editing it manually and Visual Studio refuses to show it in the designer afterwards.

Tom Robinson
  • 8,348
  • 9
  • 58
  • 102

2 Answers2

2

datetime2 does not exist is SQL Server Compact, in order to save datetime2 values, you must save in a nvarchar(27) value of the form 'YYYY-MM-DD hh:mm:ss.nnnnnnn' (see http://msdn.microsoft.com/en-us/library/ms171931.aspx)

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • Does it make more sense to have two fields: datetime (for YYYY-MM-DD hh:mm:ss) and int (for milliseconds)? You can join them in UI, but such structure has smaller size and smaller index. – Alex Klaus Apr 11 '13 at 22:55
  • Yeah, maybe it does, the format above can be easily converted back to datatime2 on the server, that is less easy if it is split.. – ErikEJ Apr 12 '13 at 06:37
1

If you take care about size of your database (especially if you've got indexes on this field), you can use a different approach.

Keep in the database two fields datetime (for YYYY-MM-DD hh:mm:ss) and smallint (for milliseconds). And join them to get a proper DateTime before displaying in UI.

In this case size of these fields will be 10 bytes (according to this source, 8 bytes of datetime + 2 bytes for smallint). Size of nvarchar(27) is 54 bytes.

Alex Klaus
  • 8,168
  • 8
  • 71
  • 87