Re: [sqlite] .net Sqlite lib reading date fields not working?
I was just using the ;DateTimeKind= DateTimeKind.Utc But I just tested without any date settings and it works also. I have not tried your new method. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Thursday, May 30, 2013 9:45 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE > > Solved it. > Great. I'm curious, which connection string properties did you end up using? > > You cannot use parameter DbType.DateTime2 and must use DbType.DateTime for > the lib to format the date upon insert. > Right, DbType.DateTime2 is not recognized by System.Data.SQLite. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
Could treat it as datetime -Original Message- From: Joe Mistachkin Sent: 5/30/2013 9:44 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE > > Solved it. > Great. I'm curious, which connection string properties did you end up using? > > You cannot use parameter DbType.DateTime2 and must use DbType.DateTime for > the lib to format the date upon insert. > Right, DbType.DateTime2 is not recognized by System.Data.SQLite. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE > > Solved it. > Great. I'm curious, which connection string properties did you end up using? > > You cannot use parameter DbType.DateTime2 and must use DbType.DateTime for > the lib to format the date upon insert. > Right, DbType.DateTime2 is not recognized by System.Data.SQLite. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
Solved it. You cannot use parameter DbType.DateTime2 and must use DbType.DateTime for the lib to format the date upon insert. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of eschneider FE Sent: Thursday, May 30, 2013 1:21 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? Joe, So are you say we can specify the exact format string in the connection string? How would that look? ; DateTimeFormatString='XXX' Would I need to download a new .NET API or Sqlite version? Eric -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Wednesday, May 29, 2013 9:58 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE wrote: > > Just realized I left in the work around code, the date issue is still a > problem. > I'm still not sure exactly what the problem actually is. That being said, I've just added a new connection string property named DateTimeFormatString to give people full control over the exact format string that will be used to parse and format DateTime objects for a particular connection. Perhaps this will assist with your issue? The changes can be seen here: https://system.data.sqlite.org/index.html/info/4f933521a1 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote: > > So are you say we can specify the exact format string in the connection > string? > As of yesterday, yes. > > How would that look? > ; DateTimeFormatString='XXX' > Probably something like: "Data Source=test.db;DateTimeFormat=CurrentCulture;DateTimeKind=Utc;DateTimeFormat String=M/d/ h:mm:ss tt;" > > Would I need to download a new .NET API or Sqlite version? > Yes, the code is only available in the Fossil source code repository at the moment. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
Joe, So are you say we can specify the exact format string in the connection string? How would that look? ; DateTimeFormatString='XXX' Would I need to download a new .NET API or Sqlite version? Eric -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Wednesday, May 29, 2013 9:58 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE wrote: > > Just realized I left in the work around code, the date issue is still a > problem. > I'm still not sure exactly what the problem actually is. That being said, I've just added a new connection string property named DateTimeFormatString to give people full control over the exact format string that will be used to parse and format DateTime objects for a particular connection. Perhaps this will assist with your issue? The changes can be seen here: https://system.data.sqlite.org/index.html/info/4f933521a1 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
I used a connection string for ;DateTimeKind= DateTimeKind.Utc but my inserts are still incorrect: "91","Eric","Schneider","Paul","11/27/1972 12:00:00 AM","1" I don't think the inserts for parameter are formatting the date. I don't believe this is UCT format. I have also confirmed that my UI that I use to display the data does not alter the date format. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Wednesday, May 29, 2013 10:58 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? Eric Schneider wrote: > > That's. For parsing, what about inserts? > 5/25/2013 1:18:20 PM The new connection string property should apply to any operation that requires converting a string to a DateTime or vice-versa (e.g. parsing or formatting). When inserting a row using a parameterized INSERT statement and a strongly typed parameter containing a DateTime, the actual DateTime object will be converted to a string prior to being physically inserted into the database. The parameters governing this conversion process are the DateTimeFormat (enum), DateTimeKind (enum), and DateTimeFormatString (string). By default, the DateTimeFormat (enum) is set to ISO8601, which will not be suitable for strings with a format like "5/25/2013 1:18:20 PM" (i.e. from your original message). Here are some possible solutions to your issue: 1. Set the DateTimeFormat connection string property to CurrentCulture. This is much more relaxed than ISO8601. Set the DateTimeKind to either Utc or Local, depending on which time zone your DateTime values are from. 2. Set the DateTimeFormatString to something that will be able to parse the DateTime values you have (e.g. "M/d/ h:mm:ss tt"). Again, you will also want to set the DateTimeKind to either Utc or Local. 3. Combine #1 and #2. Set DateTimeFormat to CurrentCulture, DateTimeKind to Utc or Local, and DateTimeFormatString to "M/d/ h:mm:ss tt". If none of the above work, there are some additional troubleshooting steps you can take to determine EXACTLY how parameter values are being bound to your statement (e.g. via the LogBind connection flag, which can also be enabled via the connection string). The documentation contains full details on the possible connection flags and their effects. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
That connection string mod doesn't seem to help. Let me know what you need from me to clarify. -Original Message- From: Joe Mistachkin Sent: 5/29/2013 9:57 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE wrote: > > Just realized I left in the work around code, the date issue is still a > problem. > I'm still not sure exactly what the problem actually is. That being said, I've just added a new connection string property named DateTimeFormatString to give people full control over the exact format string that will be used to parse and format DateTime objects for a particular connection. Perhaps this will assist with your issue? The changes can be seen here: https://system.data.sqlite.org/index.html/info/4f933521a1 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
Eric Schneider wrote: > > That's. For parsing, what about inserts? > 5/25/2013 1:18:20 PM The new connection string property should apply to any operation that requires converting a string to a DateTime or vice-versa (e.g. parsing or formatting). When inserting a row using a parameterized INSERT statement and a strongly typed parameter containing a DateTime, the actual DateTime object will be converted to a string prior to being physically inserted into the database. The parameters governing this conversion process are the DateTimeFormat (enum), DateTimeKind (enum), and DateTimeFormatString (string). By default, the DateTimeFormat (enum) is set to ISO8601, which will not be suitable for strings with a format like "5/25/2013 1:18:20 PM" (i.e. from your original message). Here are some possible solutions to your issue: 1. Set the DateTimeFormat connection string property to CurrentCulture. This is much more relaxed than ISO8601. Set the DateTimeKind to either Utc or Local, depending on which time zone your DateTime values are from. 2. Set the DateTimeFormatString to something that will be able to parse the DateTime values you have (e.g. "M/d/ h:mm:ss tt"). Again, you will also want to set the DateTimeKind to either Utc or Local. 3. Combine #1 and #2. Set DateTimeFormat to CurrentCulture, DateTimeKind to Utc or Local, and DateTimeFormatString to "M/d/ h:mm:ss tt". If none of the above work, there are some additional troubleshooting steps you can take to determine EXACTLY how parameter values are being bound to your statement (e.g. via the LogBind connection flag, which can also be enabled via the connection string). The documentation contains full details on the possible connection flags and their effects. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
That's. For parsing, what about inserts? -Original Message- From: Joe Mistachkin Sent: 5/29/2013 9:57 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE wrote: > > Just realized I left in the work around code, the date issue is still a > problem. > I'm still not sure exactly what the problem actually is. That being said, I've just added a new connection string property named DateTimeFormatString to give people full control over the exact format string that will be used to parse and format DateTime objects for a particular connection. Perhaps this will assist with your issue? The changes can be seen here: https://system.data.sqlite.org/index.html/info/4f933521a1 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote: > > Just realized I left in the work around code, the date issue is still a > problem. > I'm still not sure exactly what the problem actually is. That being said, I've just added a new connection string property named DateTimeFormatString to give people full control over the exact format string that will be used to parse and format DateTime objects for a particular connection. Perhaps this will assist with your issue? The changes can be seen here: https://system.data.sqlite.org/index.html/info/4f933521a1 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
Just realized I left in the work around code, the date issue is still a problem. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of eschneider FE Sent: Wednesday, May 29, 2013 6:26 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? The default is a non-working default though. So inserting using best practices of SQL statement & parameters does not work, and queries also. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Wednesday, May 29, 2013 6:13 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE wrote: > > Ok; re-reading last and saw connection string. So adding ;DateTimeKind= > DateTimeKind.Utc to connection string works. Why isn't this the default? > Primarily because making it the default would alter the behavior in a very subtle and non-backward compatible way. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
The default is a non-working default though. So inserting using best practices of SQL statement & parameters does not work, and queries also. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Wednesday, May 29, 2013 6:13 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE wrote: > > Ok; re-reading last and saw connection string. So adding ;DateTimeKind= > DateTimeKind.Utc to connection string works. Why isn't this the default? > Primarily because making it the default would alter the behavior in a very subtle and non-backward compatible way. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote: > > Ok; re-reading last and saw connection string. So adding ;DateTimeKind= > DateTimeKind.Utc to connection string works. Why isn't this the default? > Primarily because making it the default would alter the behavior in a very subtle and non-backward compatible way. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
Ok; re-reading last and saw connection string. So adding ;DateTimeKind=
DateTimeKind.Utc to connection string works. Why isn't this the default?
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of eschneider FE
Sent: Wednesday, May 29, 2013 5:39 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] .net Sqlite lib reading date fields not working?
I understand that Joe, but if I'm inserting using parameters; the Sqlite
.net lib is determining the format not me the developer, I'm just passing in
a DateTime. In fact other people are complaining they cannot insert using
parameters; I assume in that case the specify the check constraint.
I would also state this is an ORM, and this code works for all databases
except Sqlite; Currently Sql Server, Oracle, and MySql. I have work around
the issue, but should not have to.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: Wednesday, May 29, 2013 4:37 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote:
>
> So when using System.Data.SQLite and inserting data using a parameter of
> datetime, I believe the System.Data.SQLite .net lib is doing a ToString()
on
> datetime when it should be using a specific date format supported by
SQlite.
>
The DateTime parsing and formatting performed by System.Data.SQLite are
governed
by the DateTimeKind and DateTimeFormat properties provided in the connection
string.
The default values are "DateTimeKind.Unspecified" and
"SQLiteDateFormats.ISO8601",
respectively.
When using the ISO-8601 format, the date/time strings must conform to one of
the
"well-known" formats supported by System.Data.SQLite, specifically:
///
/// An array of ISO-8601 DateTime formats that we support parsing.
///
private static string[] _datetimeFormats = new string[] {
"THHmmssK",
"THHmmK",
"HH:mm:ss.FFFK",
"HH:mm:ssK",
"HH:mmK",
"-MM-dd HH:mm:ss.FFFK", /* NOTE: UTC default (5). */
"-MM-dd HH:mm:ssK",
"-MM-dd HH:mmK",
"-MM-ddTHH:mm:ss.FFFK",
"-MM-ddTHH:mmK",
"-MM-ddTHH:mm:ssK",
"MMddHHmmssK",
"MMddHHmmK",
"MMddTHHmmssFFFK",
"THHmmss",
"THHmm",
"HH:mm:ss.FFF",
"HH:mm:ss",
"HH:mm",
"-MM-dd HH:mm:ss.FFF", /* NOTE: Non-UTC default (19). */
"-MM-dd HH:mm:ss",
"-MM-dd HH:mm",
"-MM-ddTHH:mm:ss.FFF",
"-MM-ddTHH:mm",
"-MM-ddTHH:mm:ss",
"MMddHHmmss",
"MMddHHmm",
"MMddTHHmmssFFF",
"-MM-dd",
"MMdd",
"yy-MM-dd"
};
The DateTime.ParseExact method is used by System.Data.SQLite; therefore, if
the
DateTime string does not conform to one of the above formats, an exception
will
be generated.
--
Joe Mistachkin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
I understand that Joe, but if I'm inserting using parameters; the Sqlite
.net lib is determining the format not me the developer, I'm just passing in
a DateTime. In fact other people are complaining they cannot insert using
parameters; I assume in that case the specify the check constraint.
I would also state this is an ORM, and this code works for all databases
except Sqlite; Currently Sql Server, Oracle, and MySql. I have work around
the issue, but should not have to.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: Wednesday, May 29, 2013 4:37 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote:
>
> So when using System.Data.SQLite and inserting data using a parameter of
> datetime, I believe the System.Data.SQLite .net lib is doing a ToString()
on
> datetime when it should be using a specific date format supported by
SQlite.
>
The DateTime parsing and formatting performed by System.Data.SQLite are
governed
by the DateTimeKind and DateTimeFormat properties provided in the connection
string.
The default values are "DateTimeKind.Unspecified" and
"SQLiteDateFormats.ISO8601",
respectively.
When using the ISO-8601 format, the date/time strings must conform to one of
the
"well-known" formats supported by System.Data.SQLite, specifically:
///
/// An array of ISO-8601 DateTime formats that we support parsing.
///
private static string[] _datetimeFormats = new string[] {
"THHmmssK",
"THHmmK",
"HH:mm:ss.FFFK",
"HH:mm:ssK",
"HH:mmK",
"-MM-dd HH:mm:ss.FFFK", /* NOTE: UTC default (5). */
"-MM-dd HH:mm:ssK",
"-MM-dd HH:mmK",
"-MM-ddTHH:mm:ss.FFFK",
"-MM-ddTHH:mmK",
"-MM-ddTHH:mm:ssK",
"MMddHHmmssK",
"MMddHHmmK",
"MMddTHHmmssFFFK",
"THHmmss",
"THHmm",
"HH:mm:ss.FFF",
"HH:mm:ss",
"HH:mm",
"-MM-dd HH:mm:ss.FFF", /* NOTE: Non-UTC default (19). */
"-MM-dd HH:mm:ss",
"-MM-dd HH:mm",
"-MM-ddTHH:mm:ss.FFF",
"-MM-ddTHH:mm",
"-MM-ddTHH:mm:ss",
"MMddHHmmss",
"MMddHHmm",
"MMddTHHmmssFFF",
"-MM-dd",
"MMdd",
"yy-MM-dd"
};
The DateTime.ParseExact method is used by System.Data.SQLite; therefore, if
the
DateTime string does not conform to one of the above formats, an exception
will
be generated.
--
Joe Mistachkin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote:
>
> So when using System.Data.SQLite and inserting data using a parameter of
> datetime, I believe the System.Data.SQLite .net lib is doing a ToString()
on
> datetime when it should be using a specific date format supported by
SQlite.
>
The DateTime parsing and formatting performed by System.Data.SQLite are
governed
by the DateTimeKind and DateTimeFormat properties provided in the connection
string.
The default values are "DateTimeKind.Unspecified" and
"SQLiteDateFormats.ISO8601",
respectively.
When using the ISO-8601 format, the date/time strings must conform to one of
the
"well-known" formats supported by System.Data.SQLite, specifically:
///
/// An array of ISO-8601 DateTime formats that we support parsing.
///
private static string[] _datetimeFormats = new string[] {
"THHmmssK",
"THHmmK",
"HH:mm:ss.FFFK",
"HH:mm:ssK",
"HH:mmK",
"-MM-dd HH:mm:ss.FFFK", /* NOTE: UTC default (5). */
"-MM-dd HH:mm:ssK",
"-MM-dd HH:mmK",
"-MM-ddTHH:mm:ss.FFFK",
"-MM-ddTHH:mmK",
"-MM-ddTHH:mm:ssK",
"MMddHHmmssK",
"MMddHHmmK",
"MMddTHHmmssFFFK",
"THHmmss",
"THHmm",
"HH:mm:ss.FFF",
"HH:mm:ss",
"HH:mm",
"-MM-dd HH:mm:ss.FFF", /* NOTE: Non-UTC default (19). */
"-MM-dd HH:mm:ss",
"-MM-dd HH:mm",
"-MM-ddTHH:mm:ss.FFF",
"-MM-ddTHH:mm",
"-MM-ddTHH:mm:ss",
"MMddHHmmss",
"MMddHHmm",
"MMddTHHmmssFFF",
"-MM-dd",
"MMdd",
"yy-MM-dd"
};
The DateTime.ParseExact method is used by System.Data.SQLite; therefore, if
the
DateTime string does not conform to one of the above formats, an exception
will
be generated.
--
Joe Mistachkin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
So when using System.Data.SQLite and inserting data using a parameter of
datetime, I believe the System.Data.SQLite .net lib is doing a ToString() on
datetime when it should be using a specific date format supported by SQlite.
-Original Message-
From: eschneider FE [mailto:eschnei...@frozenelephant.com]
Sent: Tuesday, May 28, 2013 1:36 PM
To: 'sqlite-users@sqlite.org'
Subject: RE: .net Sqlite lib reading date fields not working?
I am not getting your replies Keith; I'm new so maybe it's just delayed.
To clarify, I think this is a .NET Sqlite lib problem, not a database issue;
sorry for the confusion.
I am currently inserting the data with the Sqlit .NET library using sql with
a parameter of datetime type, so If the date is being stored in the wrong
format, I would argue that would be a bug the .NET Sqlite lib?
Also since the exception is occurring with-in the .NET Sqlite lib so the
coercion is happening in the .NET lib., seems weird because I can call
datetime.parse and it works, so am not sure what the lib is doing
differently.
I'm currently using version: 1.0.86.0 of the .NET lib.
Eric
-Original Message-
From: eschneider FE [mailto:eschnei...@frozenelephant.com]
Sent: Saturday, May 25, 2013 1:32 PM
To: 'sqlite-users@sqlite.org'
Subject: .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\SqlLiteUn
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](IDataReader
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:line
468
at SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery() in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\SqlLiteTestProject_VB\SqlLiteUn
itTest.vb:line 296
Frozen Elephant Inc.
739 N. Thomphson Dr. #207
Madison, WI 53704
Eric Schneider
(414) 975-3582
eschnei...@frozenelephant.com
www.FrozenElephant.com <http://www.frozenelephant.com/>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
I am not getting your replies Keith; I'm new so maybe it's just delayed.
To clarify, I think this is a .NET Sqlite lib problem, not a database issue;
sorry for the confusion.
I am currently inserting the data with the Sqlit .NET library using sql with
a parameter of datetime type, so If the date is being stored in the wrong
format, I would argue that would be a bug the .NET Sqlite lib?
Also since the exception is occurring with-in the .NET Sqlite lib so the
coercion is happening in the .NET lib., seems weird because I can call
datetime.parse and it works, so am not sure what the lib is doing
differently.
I'm currently using version: 1.0.86.0 of the .NET lib.
Eric
-Original Message-
From: eschneider FE [mailto:eschnei...@frozenelephant.com]
Sent: Saturday, May 25, 2013 1:32 PM
To: 'sqlite-users@sqlite.org'
Subject: .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\SqlLiteUn
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](IDataReader
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:line
468
at SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery() in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\SqlLiteTestProject_VB\SqlLiteUn
itTest.vb:line 296
Frozen Elephant Inc.
739 N. Thomphson Dr. #207
Madison, WI 53704
Eric Schneider
(414) 975-3582
eschnei...@frozenelephant.com
www.FrozenElephant.com
