Solved

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

Posted on 2014-04-25
29
2,332 Views
Last Modified: 2014-05-09
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
0
Comment
Question by:Kalpesh Chhatrala
  • 12
  • 6
  • 4
  • +5
29 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

by:Kalpesh Chhatrala
Comment Utility
@ Guy Hengel [angelIII / a3]

your reply is not regarding SQLite.

i am getting problem in sqlite select query.
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 16

Author Comment

by:Kalpesh Chhatrala
Comment Utility
@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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

by:Kalpesh Chhatrala
Comment Utility
i am getting error message on execution of Query ( DataTable not generated from query).
0
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
Comment Utility
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

by:Kalpesh Chhatrala
Comment Utility
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

by:AndyAinscow
Comment Utility
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

by:Kalpesh Chhatrala
Comment Utility
i debugged my code and getting error on objAdapter.Fill(MyDataTable).
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
>>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

by:Kalpesh Chhatrala
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
>>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

by:Kalpesh Chhatrala
Comment Utility
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

by:AndyAinscow
Comment Utility
>>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 74

Expert Comment

by:käµfm³d 👽
Comment Utility
Please post the column definitions of each column requested by your query.
0
 
LVL 16

Author Comment

by:Kalpesh Chhatrala
Comment Utility
I attached here with Table Structure Screenshot.

Table Structure
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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
 
LVL 16

Author Comment

by:Kalpesh Chhatrala
Comment Utility
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
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
@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

by:Mark Wills
Comment Utility
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

by:PortletPaul
Comment Utility
>>"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

by:Mike Lazarus
Comment Utility
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

by:Kalpesh Chhatrala
Comment Utility
@GLComputing:

I tried your suggestion. its not working.
0
 
LVL 16

Accepted Solution

by:
Kalpesh Chhatrala earned 0 total points
Comment Utility
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

by:Kalpesh Chhatrala
Comment Utility
Spend too much time to resolve this issue.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now