c# 读取sqlite3中的SQL type=TIMESTAMP字段时报错

public static DateTime safeGetDateTime(SQLiteDataReader reader, string field)

{

int column = reader.GetOrdinal(field);

if (!reader.IsDBNull(column))

{

return reader.GetDateTime(column);

}

return DateTime.Now;

}

private void process(string dbPath)

{

int nNum = 0;

SQLiteConnection connection = null;

try

{

connection = new SQLiteConnection("Data Source=" + dbPath + ";version=3;new=False;datetimeformat=CurrentCulture;");

connection.Open();

SQLiteCommand command = new SQLiteCommand(connection);

command.CommandText = "select ZADDRESS,ZDURATION,ZORIGINATED, ZDATE ,Cast(ZDATE as nvarchar(20)) as ZDATE_NEW from ZCALLRECORD ;";

SQLiteDataReader reader = command.ExecuteReader();

while (reader.Read())

{

try

{

DateTime dateTime = SqliteDBTool.safeGetDateTime(reader, "ZDATE") ;

string number = Encoding.UTF8.GetString(SqliteDBTool.safeGetBytes(reader, "ZADDRESS"));

int dateTimeIndex= reader.GetOrdinal("ZDATE_NEW") ;

var date=reader["ZDATE_NEW"].ToString();

string duration = SqliteDBTool.safeGetFloat(reader, "ZDURATION").ToString("F1");

}

catch(Exception e)

{

add_log(e.ToString());

}

}

reader.Close();

connection.Close();

connection.Dispose();

}

catch (Exception e)

{

Console.WriteLine(e.Message);

if (connection != null)

{

connection.Close();

connection.Dispose();

}

}

}

process(@"C:\CallHistory.storedata");

使用SQLite抛出异常： 该字符串未被识别为有效的 DateTime 错误。

参考如下 

c# - sqlite throwing a "String not recognized as a valid datetime" - Stack Overflow

但我就想把数据读出来，就要使用Cast(ZDATE as nvarchar(20)) as ZDATE_NEW 把数据格式转一下，然后再用        var date=reader["ZDATE_NEW"].ToString(); 读出数据然后再慢慢转换

SQLite timestamp field - converting to datetime - Stack Overflowhttps://stackoverflow.com/questions/54914123/sqlite-timestamp-field-converting-to-datetime参考上文时间要加个978307200

double timestamp = 6220820844.00406+978307200;//这里时间要变换一下

            // 转换为 DateTime

            DateTimeOffset dateTimeOffset = DateTimeOffset.FromUnixTimeSeconds((long)timestamp);

            DateTime dateTime = dateTimeOffset.UtcDateTime;

            add_log(dateTime.ToString("s"));

select ZADDRESS,ZDURATION,ZORIGINATED,ZDATE ,Cast(ZDATE as nvarchar(20)) as ZDATE_NEW from  ZCALLRECORD ;

好文推荐