sqlite date and time format problem

[serkanp]

i have a sqlite3 db and inside a table i have a column named "saat" as "time" format , and a column name "tarih" as "date" format and "createdDate" as "timestamp" format. 
i am using orm 2010.3.1125.1  
when i use the wizard, it detects time , date formats as decimal 
but the timestamp format as datetime
so i changed the rlinq file , i changed decimal with datetime in "tarih" column. "saat" as timespan
also i changed the class file automatically generated.. 
 but not worked, i tried different datatypes, date, datetime etc..  both in rlinq and class file
but i get no success..

what are the correct formats for "date" and "time" formats?
 

[serkanp]

also i noticed a very interesting case.. 
i wanted to attach the db but forum does not allow it .. anyway let me tell the story...
i have a table named users
it has 3 columns
username as nvarchar 50, userlogin as nvarchar 50, createdDate as timeStamp

i have 3 records , i inserted 3 rows with orm, i set the createdDate with DateTime.Now , when i make a select query (select createdDate from users) with sqlite administrator, all createdDate columns are interestingly crazy values (one of them 30.12.1899 00:03:17 and the other is seems empty-no data-).  but the datetime.now gives 20.01.2011 13:09:00 in visual studio
but when i make a select query like "select datetime(createdDate) from users" it shows the dates correctly. 
but again interestingly, when i get the data with orm, it shows the dates correctly as i wanted.. 

then i changed one row's createdDate column manually (i am using sqlite administrator)
when i run a select command, my manually changed column shown correct but the others are wrong again in sqlite administrator. 
but now, when i get the data with orm,
     Dim t As Sqlitest = New Sqlitest
     For Each obje In t.Users
            Debug.Print(obje.UserLogin & "=" & obje.CreatedDate)
     Next
(before manually changing 1 row's createdDate column, this code worked perfect)
 it gives an interesting error : 
Error reading field ormtest1.User._createdDate from ResultSet: System.FormatException: Input string was not in a correct format.
   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)
   at System.Double.Parse(String s, NumberStyles style, NumberFormatInfo info)
   at System.Convert.ToDouble(String value, IFormatProvider provider)
   at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText)
   at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
   at System.Data.SQLite.SQLiteDataReader.GetDateTime(Int32 i)
   at OpenAccessRuntime.Data.Timestamp2DateTimeConverter.ReadValue(DataHolder& data)
   at OpenAccessRuntime.Data.Timestamp2DateTimeConverter.Read(DataHolder& data)
   at OpenAccessRuntime.Relational.metadata.RelationalColumn.GetValue(DataHolder& data)
   at OpenAccessRuntime.Relational.RelationalGenericState.GetFieldDataPass1(DataHolder& holder, RelationalField field)
   at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol) System.FormatException: Input string was not in a correct format.
   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)
   at System.Double.Parse(String s, NumberStyles style, NumberFormatInfo info)
   at System.Convert.ToDouble(String value, IFormatProvider provider)
   at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText)
   at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
   at System.Data.SQLite.SQLiteDataReader.GetDateTime(Int32 i)
   at OpenAccessRuntime.Data.Timestamp2DateTimeConverter.ReadValue(DataHolder& data)
   at OpenAccessRuntime.Data.Timestamp2DateTimeConverter.Read(DataHolder& data)
   at OpenAccessRuntime.Relational.metadata.RelationalColumn.GetValue(DataHolder& data)
   at OpenAccessRuntime.Relational.RelationalGenericState.GetFieldDataPass1(DataHolder& holder, RelationalField field)
   at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol)


what is wrong ? :)))

[Thomas]

Hi Serkan,

this is interesting. However, SQLite is a bit difficult / weak when it comes to datetime, as strings are used for the underlying representation in the database. This means, that the string value semantics takes over at times. In your case it seems to me, that 'old' dates will be falsely interpreted as double which fails.
I will look into this, maybe there is a config option for that on the driver level. 

Best wishes,
Thomas
the Telerik team

[Thomas]

Hi serkanp,

I think the problem is that the database is using the DateTimeFormat=JulianDay option, but OpenAccess wants the ISO8601 format. Julian Day is internally (in SQLite) held as a double value (most likely in a string form) and then converted to/from a System.DateTime. A database can only be opened in one way or the other and you cannot mix the different styles, and the stored (string) representation must be comparable and consistent; otherwise bad behavior can happen.
I propose to make sure that your tools and your application use the same ISO8601 format; this is also the best format for human readability.

Greetings,
Thomas
the Telerik team