Click here to Skip to main content
Rate this: bad
good
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 8:32am
rune711263
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 at 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 at 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

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



Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 22 Jul 2014
Copyright © CodeProject, 1999-2014
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