Solved

# " String was not recognized as a valid DateTime " Getting Error in SQLite Select Query

Posted on 2014-04-25
2,435 Views
Hello,

I am getting this error message in SQLite Select Query.

"String was not recognized as a valid DateTime."

I attached here with Sqlite Table Screenshot.

0
Question by:Kalpesh Chhatrala
• 12
• 6
• 4
• +5

LVL 143

Expert Comment

ID: 40022107
does this happen on the record(s) which seem "empty", or on the records which are filled?
using what exact query?

I do have an article on date/time, though not specifically for SQlite:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0

LVL 16

Author Comment

ID: 40022124
@ Guy Hengel [angelIII / a3]

i am getting problem in sqlite select query.
0

LVL 44

Expert Comment

ID: 40022195
I don't think I have ever seen the hyphen '-' in a valid date time format.
Is that field a date time field OR a string, check how the field is defined in the table.
0

LVL 143

Expert Comment

ID: 40022203
yes, I know, as a I wrote. nevertheless, the article explains on how to do "in general", sql litedoes have it's own functions work with date/times:
http://sqlite.org/lang_datefunc.html

0

LVL 16

Author Comment

ID: 40022209
@AndyAinscow

Column Format is DateTime.

Select Query

select A.*,C.LanguageNaming from tabItems A INNER JOIN tabItemLanguageAssignments B ON B.ItemID = A.ItemID INNER JOIN tabLanguages C ON C.LanguageID = B.LanguageID

Its working fine if i fill all datetime column value.

if i leave null value in Datetime column then getting error.
0

LVL 143

Expert Comment

ID: 40022216
do you get the error on running the query, or on using the data further down some other code?
you may then need to check against and handle the "null" value:
http://msdn.microsoft.com/en-us/library/system.dbnull.value%28v=vs.110%29.aspx
0

LVL 16

Author Comment

ID: 40022229
i am getting error message on execution of Query ( DataTable not generated from query).
0

LVL 80

Expert Comment

ID: 40022365
so if the table has a NULL in the PublishingDateTime you get an error. Then you have to check for a NULL value and handle it differently.
0

LVL 16

Author Comment

ID: 40022385
SQLite Database is not in my control. i am getting this database from Server and after that i need to use this db.
0

LVL 44

Expert Comment

ID: 40022441
I'm puzzled.
IMHO the query you posted should not fail with that error because that field is not involved in a condition or link to another table.  I suspect Guy Hengel has exactly that same view based on his last comment.

Have you single stepped the code (or used some other mechanism) to make certain it fails when the line of code calling the query is executed or is this a guess on your part.
0

LVL 16

Author Comment

ID: 40022450
i debugged my code and getting error on objAdapter.Fill(MyDataTable).
0

LVL 143

Expert Comment

ID: 40022461
>Column Format is DateTime.
http://www.sqlite.org/datatype3.html
1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.


so, what is your "storage class" for the field?
I presume that you cannot just query a real "date" data typed field in .net directly from SQLlite... you can only cast afterwards.
0

LVL 44

Expert Comment

ID: 40022481
>>i debugged my code and getting error on objAdapter.Fill(MyDataTable).

I suspect the error is after the query has run.

To me it looks like your 'MyDataTable' field can not cope with a null value - check how you define the fields (columns) in that and adjust if necessary.
0

LVL 16

Author Comment

ID: 40022759
MyDataTable is Datatable Object (its not field).

My Code snippet

try {
using (SQLiteDataAdapter od = new SQLiteDataAdapter("select A.*,C.LanguageNaming from tabItems A INNER JOIN tabItemLanguageAssignments B ON B.ItemID = A.ItemID INNER JOIN tabLanguages C ON C.LanguageID = B.LanguageID", oConn)) {
using (DataTable dt = new DataTable())
{
od.Fill(dt);

}
}
} catch (Exception ex) {
LastError = ex.Message.ToString();

}

0

LVL 44

Expert Comment

ID: 40022845
>>MyDataTable is Datatable Object (its not field).

Yes, but the table has multiple fields (also known as columns) in it.  One of those you use to store the date time returned in the query - as you have found the returned value is sometimes a null value.
0

LVL 16

Author Comment

ID: 40022874
may be you did not check my code snippet.

i did not defined any column in DataTable. its automatic generated by "Fill" command.
0

LVL 44

Expert Comment

ID: 40023013
>>may be you did not check my code snippet.
>>i did not defined any column in DataTable.

Yes I did check it.

Odd.
Something somewhere is attempting to convert into a Date Time.  As the field type is to be automatically generated then it should not attempt to convert to a Date Time if it was not in the correct format but keep it as the source type.

ps. I note also the earlier comment from Guy Hengel where the source is either a string, an integer or a floating point number - none of which are Date Time - again speaking against some sort of auto conversion into an incorrect type.
0

LVL 75

Expert Comment

ID: 40023233
Please post the column definitions of each column requested by your query.
0

LVL 16

Author Comment

ID: 40023325
I attached here with Table Structure Screenshot.

0

LVL 75

Expert Comment

ID: 40023479
Which Sqlite library (and version) are you using to connect to Sqlite from .NET? I just downloaded 1.0.92.0 of the official Sqlite engine, and I did not encounter the same issue that you did--granted I had to scale down my query since you only showed one table structure:

e.g.

using System;
using System.Data;
using System.Data.SQLite;

namespace ConsoleApplication211
{
class Program
{
static void Main(string[] args)
{
try
{
using (SQLiteConnection oConn = new SQLiteConnection(@"Data Source=C:\Users\kaufmak\Desktop\ee.sqlite"))
{
{
using (DataTable dt = new DataTable())
{
od.Fill(dt);
System.Diagnostics.Debugger.Break();
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());

}
}
}
}


0

LVL 16

Author Comment

ID: 40023505

I attached here with my db file but use it without editing in any editor (means direct in Visual studio project).

https://www.dropbox.com/s/im6hol5hlzp4931/SLAppDataBaseV04.zip
0

LVL 44

Expert Comment

ID: 40024174
@kaufmed
With your table screenshot you have the first record with a date and the second with a null.  In the problem table it is the other way round - does that make a difference in your test ?
0

LVL 51

Expert Comment

ID: 40035782
First up, I don't have specific experience in this problem. But have had similar problems.

So, a couple of thoughts to share...

1) check how you are connecting to SQLLite for any options for Date (make sure it is ISO8601)
2) also check to see if there is an option for "blank if NULL" (and get rid of it)
3) when doing the SELECT, make sure you select each column explicitly and use datetime(itempublishingdatetime) and ifnull() instead of A.*
0

LVL 48

Expert Comment

ID: 40036271
>>"make sure you select each column explicitly"

hear, hear!
by itself this can be a cause of error
(e.g. when folks assume the columns are a then b then c but actually get b the c then a)
0

LVL 30

Expert Comment

ID: 40038179
I encountered this error back in 2011 with .Net and MS-SQL ... and only in a small number of countries.

The fix, for me, was to change the Regional Settings in Windows Control Panel from "am" and "pm" to "AM" and "PM" - some country versions of Windows set it differently

Not sure if that will work for you, but try messing with the Regional or Locale settings to test - I see your profile says you're in India, so maybe try an install (VM?) as fully US?
0

LVL 16

Author Comment

ID: 40040672
@GLComputing:

I tried your suggestion. its not working.
0

LVL 16

Accepted Solution

Kalpesh Chhatrala earned 0 total points
ID: 40040758
I Added IfNull condition in query and its working.

Select Query Example

select A.ItemID,A.ItemCatetegoryID,A.ItemType,A.ItemTargetOS,A.ItemName,A.ItemSizeInKB,A.ItemAdminNotes,A.ItemEncryptPassword,A.ItemFileName,A.ItemFavorite,A.ItemRead,A.ItemDeleted,ifnull(A.ItemPublishingDateTime,'1900-01-01') as ItemPublishingDateTime,A.SSMA_TimeStamp,A.ItemSelected,C.LanguageNaming from tabItems A INNER JOIN tabItemLanguageAssignments B ON B.ItemID = A.ItemID INNER JOIN tabLanguages C ON C.LanguageID = B.LanguageID
0

LVL 16

Author Closing Comment

ID: 40053095
Spend too much time to resolve this issue.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question