SQLite does not have a datetime type. Your declaration of a field as "Date datetime NOT NULL" is equivalent to a declaration of "Date BLOB NOT NULL" as far as SQLite is concerned.
http://www.sqlite.org/datatype3.html This means that whatever :blob of data: you are storing in the "Date" field is being returned to you in exactly the same binary format in which it was stored in the database in the first place without coercion by SQLite. The error is an application error because it cannot parse the returned data thus returning the error message: "System.FormatException: String was not recognized as a valid DateTime." If the data was stored by your application, then you application is incapable of decoding what it encoded. If you stored the data manually, then you stored it in an incorrect format which your application cannot decode. This does not surprise me. You are using a Microsoft product and your selected "datetime" string representation format is ambiguous. I would suggest you store datetime information in (any) database as either a Julian (Floating Point) [either consistently JD or MJD as you might choose], or as a unix epoch integer/float -- both of which store the information in UT1. Your application can then convert to and from whatever ambiguous local format you wish to use to converse with the applications' users as necessary. In a pinch, SQLite can also do conversions to/from either unix epoch or Julian Dates within the limitations of the underlying OS time & date handling functions (which are very broken on Microsoft OS's) if you need to "look at / manipulate" the data without your application, such as from the shell. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: [email protected] [mailto:sqlite-users- > [email protected]] On Behalf Of eschneider FE > Sent: Saturday, 25 May, 2013 12:32 > To: [email protected] > Subject: [sqlite] .net reading date fields not working? > > Hello, > > Having problem reading dates using a data reader. Looks like a bug in > SqLite? > > Schema: > > CREATE TABLE [Errors] ( > "ErrorId" integer PRIMARY KEY AUTOINCREMENT NOT NULL, > "Date" datetime NOT NULL, > "Error" text(2147483647) NOT NULL COLLATE NOCASE > > ) > > Data: > "1" "5/25/2013 1:18:20 PM" "Test 333 5/25/2013 1:18:20 PM" > "2" "5/25/2013 1:18:30 PM" "Test 333 5/25/2013 1:18:30 PM" > > > Code: > Public Shared Function ObtainColumnValue(ByVal column As > DatabaseColumn, ByVal reader As IDataReader) As Object > If column Is Nothing Then Throw New > ArgumentNullException("column") > If reader Is Nothing Then Throw New > ArgumentNullException("reader") > > If column.DatabaseColumnIndex <= 0 Then > Try > column.DatabaseColumnIndex = > reader.GetOrdinal(column.DatabaseColumnName) > > Catch ex As IndexOutOfRangeException > Throw New DatabaseColumnNotFoundException("Column: " + > column.DatabaseColumnName + " not found for property: " + > column.MappedPropertyName, ex) > End Try > End If > > Return reader(column.DatabaseColumnIndex) > > End Function > > > Error: > > Test Name: TestSimpleQuery > Test FullName: SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery > Test Source: > D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\SqlLiteTestProject_VB\SqlLite > Un > itTest.vb : line 290 > Test Outcome: Failed > Test Duration: 0:00:00.04931 > > Result Message: > Test method SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery threw > exception: > System.FormatException: String was not recognized as a valid DateTime. > Result StackTrace: > at System.DateTime.ParseExact(String s, String[] formats, IFormatProvider > provider, DateTimeStyles style) > at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, > SQLiteDateFormats format, DateTimeKind kind) > at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 > index) > at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, > SQLiteConnectionFlags flags, Int32 index, SQLiteType typ) > at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i) > at > FrozenElephant.Symbiotic.DataReaderHelper.ObtainColumnValue(DatabaseColumn > column, IDataReader reader) in > D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\DataReaderHelper.vb > :l > ine 147 > at FrozenElephant.Symbiotic.DataReaderHelper.LoadItem[T](T newItem, > IDataReader reader, IList`1 columns) in > D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\DataReaderHelper.vb > :l > ine 102 > at > FrozenElephant.Symbiotic.DataReaderHelper.DataReaderToObject[T](IDataReade > r > reader, IList`1 columns) in > D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\DataReaderHelper.vb > :l > ine 91 > at > FrozenElephant.Symbiotic.ObjectLoader.ObtainItems[T](IDatabaseTypesFactory > factory, ISqlQuery sql) in > D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\ObjectLoader.vb:lin > e > 468 > at SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery() in > D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\SqlLiteTestProject_VB\SqlLite > Un > itTest.vb:line 296 > > Frozen Elephant Inc. > 739 N. Thomphson Dr. #207 > Madison, WI 53704 > Eric Schneider > (414) 975-3582 > [email protected] > www.FrozenElephant.com <http://www.frozenelephant.com/> > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
