sqlite抛出“未被识别为有效日期时间的字符串” [英] sqlite throwing a "String not recognized as a valid datetime"

问题描述

我正在玩Sqlite，并在尝试读回一些测试数据时不断收到错误。例如，我使用单个表和一些列创建了一个简单的数据库，并用一些测试数据填充它，如下所示。

  sqlite> .schema 
 CREATE TABLEshows（id integer primary key asc autoincrement，showName TEXT，guest TEXT，dateAired date，dateWatched date）; 
 
 sqlite>选择*从节目; 
 6 | test show | test guest 1 | 2012.05.01 | 2012.07.10 
 7 | test show | test guest 2 | 2012.05.02 | 2012.07.10 
 8 | test show | test guest 4 | 2012.05.04 | 2012.07.10 
  

我使用System.Data.Sqlite库此处，但它一直给我一个错误同时尝试读取日期列。我试图把日期以dd-MM-yyyy格式，但仍然得到一个错误说字符串不被认为是有效的日期时间。我已经尝试使用DateTime.Parse或将其转换为datetime或只是ToString（）它看到发生了什么，但我继续得到相同的错误。我可以读取文本字段很好，但不能读取日期字段。

我的C＃代码已剪切如下

  var sqliteConn = new SQLiteConnection（Data Source = data / shows.db; Version = 3; New = False; Compress = True）; 
 sqliteConn.Open（）; 
 SQLiteCommand cmd = new SQLiteCommand（sqliteConn）; 
 cmd.CommandText =select * from shows; 
 
 SQLiteDataReader reader = cmd.ExecuteReader（）; 
 while（reader.Read（））
 {
 var showName = reader [showName]; 
 //这是它给我一个错误。 
 //我已经尝试过各种各样的事情，如DateTime.Parse 
 var airedDate = DateTime.ParseExact（yyyy.MM.dd，reader [dateAired]。ToString（），new CultureInfo en-AU））; 
} 
 reader.Close（）; 
  

任何帮助将非常感激。

Regards，

解决方案

根据这个线程，我决定构造我的连接字符串与datetimeformat参数和字符串未确认为有效的datetime问题已解决。 / p>

我的示例连接字符串：

  source =< source to db文件> ;; version = 3; new = False; datetimeformat = CurrentCulture 
  

.87.0（2013年7月8日）（请参阅发行说明）

I am playing around with Sqlite and keep getting an error when trying to read back some test data. For example, I created a simple db with a single table and some columns and populated it with some test data as shown below.

sqlite> .schema
CREATE TABLE "shows"(id integer primary key asc autoincrement, showName TEXT, guest TEXT, dateAired date, dateWatched date);

sqlite> select * from shows;
6|test show|test guest 1|2012.05.01|2012.07.10
7|test show|test guest 2|2012.05.02|2012.07.10
8|test show|test guest 4|2012.05.04|2012.07.10

I am using the System.Data.Sqlite library available here , but it keeps giving me an error while trying to read the date column. I tried putting the dates in the dd-MM-yyyy format, but still get an error saying "String Not Recognized as a valid datetime." I have tried using DateTime.Parse or casting it to datetime or just ToString()'ing it to see what happens, but I keep getting the same error. I can read the text fields fine, but can't read the date fields.

My C# code snipped is given below

var sqliteConn = new SQLiteConnection("Data Source=data/shows.db;Version=3;New=False;Compress=True");
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand(sqliteConn);
cmd.CommandText = "select * from shows";

SQLiteDataReader reader = cmd.ExecuteReader( );
while (reader.Read( ))
    {
    var showName = reader["showName"];
    // This is where it keeps giving me an error.
    // I have tried various things such as DateTime.Parse
    var airedDate = DateTime.ParseExact("yyyy.MM.dd", reader["dateAired"].ToString(), new CultureInfo("en-AU"));
     }
reader.Close( );

Any help would be greatly appreciated.

Regards,

解决方案

Based on the discussion in this thread, I decided to construct my connection string with the "datetimeformat" parameter and the "String Not Recognized as a valid datetime" issue was resolved.

My example connection string:

source=<source to db file>;version=3;new=False;datetimeformat=CurrentCulture

This feature was released in version 1.0.87.0 on July 8, 2013 (see release notes)

这篇关于sqlite抛出“未被识别为有效日期时间的字符串”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！