Link to home
Start Free TrialLog in
Avatar of Kalpesh Chhatrala
Kalpesh ChhatralaFlag for India

asked on

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

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.

User generated image
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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:
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
Avatar of Kalpesh Chhatrala

ASKER

@ Guy Hengel [angelIII / a3]

your reply is not regarding SQLite.

i am getting problem in sqlite select query.
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.
>your reply is not regarding SQLite.
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

and I requested more information about your query, please post them, unless you find the issue with above link and/or my article general approach
@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.
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
i am getting error message on execution of Query ( DataTable not generated from query).
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.
SQLite Database is not in my control. i am getting this database from Server and after that i need to use this db.
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.
i debugged my code and getting error on objAdapter.Fill(MyDataTable).
>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.

Open in new window


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.
>>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.
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();
	 
}

Open in new window

>>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.
may be you did not check my code snippet.

i did not defined any column in DataTable. its automatic generated by "Fill" command.
>>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.
Please post the column definitions of each column requested by your query.
I attached here with Table Structure Screenshot.

User generated image
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 (SQLiteDataAdapter od = new SQLiteDataAdapter("select A.* from tabItems A", oConn))
                    {
                        using (DataTable dt = new DataTable())
                        {
                            od.Fill(dt);
                            System.Diagnostics.Debugger.Break();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());

            }
        }
    }
}

Open in new window


User generated image
User generated image
i am using latest SQLite Connector. downloaded it from SQLite website.

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

Db file sharing is not allowed here. so you can download it from below link

https://www.dropbox.com/s/im6hol5hlzp4931/SLAppDataBaseV04.zip
@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 ?
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.*
>>"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)
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?
@GLComputing:

I tried your suggestion. its not working.
ASKER CERTIFIED SOLUTION
Avatar of Kalpesh Chhatrala
Kalpesh Chhatrala
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Spend too much time to resolve this issue.