Solved

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

Posted on 2014-04-25
29
2,435 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 143

Expert Comment

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

by:Kalpesh Chhatrala
ID: 40022124
@ 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
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 143

Expert Comment

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

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

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

Expert Comment

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

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

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

by:Kalpesh Chhatrala
ID: 40022450
i debugged my code and getting error on objAdapter.Fill(MyDataTable).
0
 
LVL 143

Expert Comment

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

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

by:Kalpesh Chhatrala
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();
	 
}

Open in new window

0
 
LVL 44

Expert Comment

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

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

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

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

Author Comment

by:Kalpesh Chhatrala
ID: 40023325
I attached here with Table Structure Screenshot.

Table Structure
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
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 (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
ID: 40023505
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
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

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

by:PortletPaul
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

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

by:Kalpesh Chhatrala
ID: 40040672
@GLComputing:

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

Accepted Solution

by:
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

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help Creating PowerShell Script 5 56
Where on a calculated field 1 23
RAISERROR WITH NOWAIT 2 18
Can mariadb access another server/database? 19 42
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

821 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