Click here to Skip to main content
12,075,542 members (66,684 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# SQLite
I give up.

I am trying to read a date value from a SQLite database. The column is defined as a DATETIME type. I sucessfully write the data to the database but when trying to read it throws the error: "
String was not recognized as a valid DateTime.
" My code looks like this:

SQLiteConnection conn = new SQLiteConnection(this.ConnString);
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(conn);
            cmd.CommandText = "SELECT * FROM tblAutomatedSearches";
            SQLiteDataReader reader = cmd.ExecuteReader();
            this.DBID = Convert.ToInt32(reader["ID"]);
            this.NotifyEmail = reader["notifyEmail"].ToString();
            this.SearchTitle = reader["jobTitle"].ToString();
            Object tempObject = reader["endDate"]; // <- throws the error
            string tempString = Convert.ToString(reader["endDate"]); //<- will throw the error also if above commented out

The date is in the format of "2012/06/11 12:51:14".

I understand that the SQLite engine actually stores the date value as a string. So it seems like the format that I write it in cant be read. I have tried EVERY format I can think of, e.g. MM/dd/yy, MM/dd/yyyy, M/d/yy, etc.

I am writing the date to the DB using a paramerterized query, ala':

SQLiteConnection conn = new SQLiteConnection(this.ConnString);
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(conn);
            cmd.CommandText = "INSERT INTO tblAutomatedSearches (jobTitle, endDate, notifyEmail, caseNumber) VALUES(@jobTitle,@endDate,@notifyEmail, @caseNumber)";
            cmd.Parameters.AddWithValue("@jobTitle", this.SearchTitle);
            cmd.Parameters.AddWithValue("@endDate", String.Format("{0:yyyy/MM/dd HH:mm:ss}", this.EndDate)); // <- this.EndDate is a DateTime type
            cmd.Parameters.AddWithValue("@notifyEmail", this.NotifyEmail);
            cmd.Parameters.AddWithValue("@caseNumber", this.CaseNumber);
            cmd.ExecuteNonQuery();

These inserts run just fine, so I'm left to think that the database is just entering the date as a string, but trying to read it as a date when retrieving it. I must be missing something. I will need to be able to sort based upon the date of these entries, so I can't just enter them as text.

You help is greatly appreciated.
Posted 12-Feb-12 9:32am
rune711296
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Why not just let date/time be a DateTime class?
DateTime endDate = (DateTime)reader["endDate"];

Converting a Date/Time to and from a string makes no sense. Only convert a Date/Time to a string, when you have to display it for the end user.
  Permalink  
v2
Comments
rune711 12-Feb-12 16:52pm
   
Success. At first I was going to comment that your suggestion wouldn't work because the EndDate object is by it's nature stored as a string in the database, but by inserting it using:

<pre lang="c#">cmd.Parameters.AddWithValue("@endDate", this.EndDate)</pre>

...appears to format it correctly to the format:

<pre>2012-06-11 15:41:56.2806071</pre>

This then correctly reads from the databse using:

<pre lang="c#">DateTime theDate = (DateTime)reader["endDate"];</pre>

Way easier anyway. You might notice that the format is different in regards to using "-" seperators instead of "/". I wonder if my old way would have worked had I parsed it like this? Regardless, this is much easier. Thanks for the assist!

Rune
Kim Togo 13-Feb-12 2:09am
   
That is good.
Yes it might work. I somethings use DateTime.ToString("s").
The Sortable ("s") Format Specifier
The custom format string is always "yyyy'-'MM'-'dd'T'HH':'mm':'ss", disregards local culture settings on the computer.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

DateTime endDate = Convert.ToDateTime(reader["endDate"].ToString());

or

string tempString = Convert.ToDateTime(reader["endDate"].ToString()).ToString();
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Error "String was not recognized as a valid DateTime."

Change your system date time format to you database fild datetime format.

i hope it help you....
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160208.1 | Last Updated 25 Aug 2015
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100