Bug 40603 - Mono can't parse Date in DB wich is in format: "2016-02-04 10:39:11Z"
Summary: Mono can't parse Date in DB wich is in format: "2016-02-04 10:39:11Z"
Status: RESOLVED FIXED
Alias: None
Product: Class Libraries
Classification: Mono
Component: Mono.Data.Sqlite (show other bugs)
Version: master
Hardware: All All
: --- normal
Target Milestone: Untriaged
Assignee: Bugzilla
URL:
Depends on:
Blocks:
 
Reported: 2016-04-21 12:15 UTC by Jochen Kühner
Modified: 2016-04-24 21:46 UTC (History)
1 user (show)

Tags:
Is this bug a regression?: ---
Last known good build:

Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.


Please create a new report on GitHub or Developer Community with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:
Status:
RESOLVED FIXED

Description Jochen Kühner 2016-04-21 12:15:25 UTC
The sqlite driver under windows System.Data.Sqlite saved DateTime in Format "2016-02-04 10:39:11Z". If I open a database with this Value in Mono, I got the Exception: 

System.FormatException: String was not recognized as a valid DateTime.
  at System.DateTimeParse.ParseExactMultiple (System.String s, System.String[] formats, System.Globalization.DateTimeFormatInfo dtfi, DateTimeStyles style) [0x00000] in <filename unknown>:0 
  at System.DateTime.ParseExact (System.String s, System.String[] formats, IFormatProvider provider, DateTimeStyles style) [0x00000] in <filename unknown>:0 
  at Mono.Data.Sqlite.SqliteConvert.ToDateTime (System.String dateText) [0x00000] in <filename unknown>:0 
  at Mono.Data.Sqlite.SqliteConvert.ToDateTime (IntPtr ptr, Int32 len) [0x00000] in <filename unknown>:0 
  at Mono.Data.Sqlite.SQLite3.GetDateTime (Mono.Data.Sqlite.SqliteStatement stmt, Int32 index) [0x00000] in <filename unknown>:0 
  at Mono.Data.Sqlite.SQLite3.GetValue (Mono.Data.Sqlite.SqliteStatement stmt, Int32 index, Mono.Data.Sqlite.SQLiteType typ) [0x00000] in <filename unknown>:0 
  at Mono.Data.Sqlite.SqliteDataReader.GetValue (Int32 i) [0x00000] in <filename unknown>:0 
  at Mono.Data.Sqlite.SqliteDataReader.get_Item (Int32 i) [0x00000] in <filename unknown>:0
Comment 1 Jochen Kühner 2016-04-21 12:21:13 UTC
It could be fixed if we change:

 private static string[] _datetimeFormats = new string[] {
      "THHmmss",
      "THHmm",
      "HH:mm:ss",
      "HH:mm",
      "HH:mm:ss.FFFFFFF",
      "yy-MM-dd",
      "yyyy-MM-dd",
      "yyyy-MM-dd HH:mm:ss.FFFFFFF",
      "yyyy-MM-dd HH:mm:ss",
      "yyyy-MM-dd HH:mm",                               
      "yyyy-MM-ddTHH:mm:ss.FFFFFFF",
      "yyyy-MM-ddTHH:mm",
      "yyyy-MM-ddTHH:mm:ss",
      "yyyyMMddHHmmss",
      "yyyyMMddHHmm",
      "yyyyMMddTHHmmssFFFFFFF",
      "yyyyMMdd"
    };

in SQliteConvert.cs
to the same as in "system.Data.Sqlite"

private static string[] _datetimeFormats = new string[] {
      "THHmmssK",
      "THHmmK",
      "HH:mm:ss.FFFFFFFK",
      "HH:mm:ssK",
      "HH:mmK",
      "yyyy-MM-dd HH:mm:ss.FFFFFFFK", /* NOTE: UTC default (5). */
      "yyyy-MM-dd HH:mm:ssK",
      "yyyy-MM-dd HH:mmK",
      "yyyy-MM-ddTHH:mm:ss.FFFFFFFK",
      "yyyy-MM-ddTHH:mmK",
      "yyyy-MM-ddTHH:mm:ssK",
      "yyyyMMddHHmmssK",
      "yyyyMMddHHmmK",
      "yyyyMMddTHHmmssFFFFFFFK",
      "THHmmss",
      "THHmm",
      "HH:mm:ss.FFFFFFF",
      "HH:mm:ss",
      "HH:mm",
      "yyyy-MM-dd HH:mm:ss.FFFFFFF", /* NOTE: Non-UTC default (19). */
      "yyyy-MM-dd HH:mm:ss",
      "yyyy-MM-dd HH:mm",
      "yyyy-MM-ddTHH:mm:ss.FFFFFFF",
      "yyyy-MM-ddTHH:mm",
      "yyyy-MM-ddTHH:mm:ss",
      "yyyyMMddHHmmss",
      "yyyyMMddHHmm",
      "yyyyMMddTHHmmssFFFFFFF",
      "yyyy-MM-dd",
      "yyyyMMdd",
      "yy-MM-dd"
    };
Comment 2 Jochen Kühner 2016-04-21 17:49:34 UTC
P.S. seams like the Mono-Sqlite is somewhat outdated. Is there no merge of the code from system.data.sqlite?

For example Mono also does not support DateTimeKind=UTC
Comment 3 Jochen Kühner 2016-04-24 21:46:46 UTC
https://github.com/mono/mono/pull/2941