" 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.

SQLite Table Screenshot
LVL 16
Kalpesh ChhatralaSoftware ConsultantAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kalpesh ChhatralaConnect With a Mentor Software ConsultantAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
@ Guy Hengel [angelIII / a3]

your reply is not regarding SQLite.

i am getting problem in sqlite select query.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
0
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
@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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
i am getting error message on execution of Query ( DataTable not generated from query).
0
 
David Johnson, CD, MVPOwnerCommented:
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
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
SQLite Database is not in my control. i am getting this database from Server and after that i need to use this db.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
i debugged my code and getting error on objAdapter.Fill(MyDataTable).
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>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
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
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

0
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>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
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
may be you did not check my code snippet.

i did not defined any column in DataTable. its automatic generated by "Fill" command.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>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
 
käµfm³d 👽Commented:
Please post the column definitions of each column requested by your query.
0
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
I attached here with Table Structure Screenshot.

Table Structure
0
 
käµfm³d 👽Commented:
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


Screenshot
Screenshot
0
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
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
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
@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
 
Mark WillsTopic AdvisorCommented:
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
 
PaulCommented:
>>"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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
@GLComputing:

I tried your suggestion. its not working.
0
 
Kalpesh ChhatralaSoftware ConsultantAuthor Commented:
Spend too much time to resolve this issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.