SQLite and .net String was not recognized as a valid DateTime

Question

I'm using VB.NET and VS 2008, and having a problem selecting dates.

In the appointmentsmain table are 2 fields, StartDate and CustomerName

The StartDate contains dates formatted in this way ..

2013-01-29 11:16:41

Dim strSQL As String = "select * from  AppointmentsMain"

Selecting all as above and reading via a DataReader throws the error. If I select the customername field only, it works fine.

I am using the System.Data.SQLite wrapper.

Below is the code. It breaks on the msgbox. Try

    Dim cn As SQLiteConnection

    Dim cmd As SQLiteCommand

    Dim rdr As SQLiteDataReader

    Dim strSQL As String = "select * from  AppointmentsMain"

    cn = New SQLiteConnection(strPublicConnectionString)

    cn.Open()

    cmd = New SQLiteCommand(strSQL, cn)

    rdr = cmd.ExecuteReader

    If rdr.Read Then

    MsgBox(rdr("startdate"))

    End If

    rdr.Close()

    cn.Close()

    Catch ex As Exception

    End Try

Did you see the question on [using a datareader to read dates](http://stackoverflow.com/q/5619216/425809)? It might apply. — Richard, Jan 29 '13 at 17:33
The problem is specific to the way SQLite handles dates. The code works fine using MSSQL.. — user2022504, Jan 29 '13 at 18:38

score 0 · Answer 1 · answered Jan 29 '13 at 18:43

0

As Matthew said, the first thing to check is the value being returned by rdr("startdate"). Your question refers to it as StartDate but your code refers to it as startdate, so perhaps it is something related to case sensitivity. You could try using rdr("StartDate") instead.

answered Jan 29 '13 at 18:43

Mun

14,098
11
59
83

The code works fine using the MSSQL or SQLServerCE name space. In other words, no problem using System.Data.SQLClient or System.Data.SQLCe and their datareaders (and databases, of course). Switching to System.Data.SQLite and using a SQLite db is when the issue occurs. – user2022504 Jan 29 '13 at 18:53
@user2022504 Have you tried using the correct casing anyway to remove that as a potential candidate for the error you are experiencing? What is the exact error message being thrown when you use rdr("startdate") ? – Mun Jan 29 '13 at 20:01
Yes, tried switching casing.. here is the exact error .. {"String was not recognized as a valid DateTime."} – user2022504 Jan 29 '13 at 21:35

score -1 · Answer 2 · answered Sep 27 '13 at 21:18

-1

I had this issue as well. From what I've read it seems somewhere along the way incompatible character encoding may have been applied to the date. I was able to solve the problem by using something like this:

SELECT DATE(StartDate) AS StartDate, ... FROM AppointmentsMain

I know this is an old question but I couldn't find a solution anywhere...

answered Sep 27 '13 at 21:18

Jon

1
2

2

This would only be a temporary solution, it would be better to make sure the dates are being stored correctly in the first place. Remember SQLite stores them as strings. – Jon Sep 27 '13 at 21:22

SQLite and .net String was not recognized as a valid DateTime

2 Answers2