Reading an SQLite DateTime value from database and assigning it to a C# string variable

Question

I have a database with a datatable which includes a DateTime column among other things. When using SQL server, I could read the DateTime value from the database using the following code:

    SqlCommand getdate = new SqlCommand("SELECT * FROM EMPinfo WHERE id = @employeeId", connect);
                getdate.Parameters.AddWithValue("@employeeId", listViewEmployee.SelectedItems[0].SubItems[2].Text);
                getdate.Connection = connect;
                connect.Open();
                SqlDataReader readList = getdate.ExecuteReader(CommandBehavior.CloseConnection);
                while (readList.Read())
                {
                    lblEmpDob.Text = ((DateTime)readList["dob"]).ToString("d");
                }

After changing the code to run with SQLite:

    SQLiteConnection connect = new SQLiteConnection(@"Data Source=quotevodata.db;");
                SQLiteCommand getlistname = new SQLiteCommand("SELECT * FROM EMPinfo WHERE id = @employeeId", connect);
                getlistname.Parameters.AddWithValue("@employeeId", listViewEmployee.SelectedItems[0].SubItems[2].Text);
                getlistname.Connection = connect;
                connect.Open();
                SQLiteDataReader readList = getlistname.ExecuteReader(CommandBehavior.CloseConnection);
                while (readList.Read())
                {
                     lblEmpDob.Text = ((DateTime)readList["dob"]).ToString("d");

                }

I keep getting the following error: "String was not recognized as a valid datetime."

I've tried different combinations and declaration of variables but it's not working out. What is the correct configuration to read DateTime values out of an SQLite database?

Have you tried to use Convert.ToDateTime(readList["dob"]) instead of simple cast? — erikscandola
– erikscandola, Commented Mar 6, 2017 at 16:21
@erikscandola what you posted is the "simple cast", ie the default conversion to DateTime. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 6, 2017 at 16:25

JonBee · Accepted Answer · 2017-03-06 16:43:25Z

8

SQLite does not have a built-in DateTime object, but rather stores them as Text, Real, or Int values.

From your error, you can infer that it's outputting as text; Which according to SQLite documentation should be in the format of "YYYY-MM-DD HH:MM:SS.SSS"

There are various ways you could parse this to a DateTime object, but I'll use RegEx:

public static DateTime ConvertToDateTime(string str)
{
    string pattern = @"(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})\.(\d{3})";
    if (Regex.IsMatch(str, pattern))
    {
        Match match = Regex.Match(str, pattern);
        int year = Convert.ToInt32(match.Groups[1].Value);
        int month = Convert.ToInt32(match.Groups[2].Value);
        int day = Convert.ToInt32(match.Groups[3].Value);
        int hour = Convert.ToInt32(match.Groups[4].Value);
        int minute = Convert.ToInt32(match.Groups[5].Value);
        int second = Convert.ToInt32(match.Groups[6].Value);
        int millisecond = Convert.ToInt32(match.Groups[7].Value);
        return new DateTime(year, month, day, hour, minute, second, millisecond);
    }
    else
    {
        throw new Exception("Unable to parse.");
    }
}

docs: http://www.sqlite.org/datatype3.html

answered Mar 6, 2017 at 16:43

JonBee

2491 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

palm-alchemist Over a year ago

thanks for the suggestion, but this seems to convert a date in string format to the DateTime format which is not what I'm trying to accomplish. I want to read a date, which saves onto the database perfectly fine in the DateTime format, from the database and display it using a label in the string format.

JonBee Over a year ago

@pallMallchemist The object being returned by your database is clearly a string. Try adding this function and changing the line of code to lblEmpDob.Text = ConvertToDateTime(readList["dob"]).ToString("d");

Flimtix Over a year ago

SQLite with Xamarin (Android/iOS) stores DateTime in binary format. For me it worked when I read out the data as follows: DateTime.FromBinary(dataReader.GetInt64(ordinal)) You can then convert this date into a string as usual.

palm-alchemist · Accepted Answer · 2017-03-07 06:15:59Z

5

Thanks for the answers, I finally got it to work by changing the INSERT statement to SQLite format as suggested:

    string empDob = dateDOB.Value.ToString("yyyy-MM-dd");
    //I then inserted this string into the database with the column configured as a "DATE" datatype.

After that, I used the following statements to read and format the date to usable string and it worked beautifully:

    DateTime dateOfBirthEmp = DateTime.Parse(readList["dob"].ToString());

    lblEmpDob.Text = dateOfBirthEmp.ToString("d");

I really appreciate the help.

answered Mar 7, 2017 at 6:15

palm-alchemist

1631 gold badge2 silver badges13 bronze badges

1 Comment

Mr Heelis Over a year ago

it's actually dateDOB.ToString("yyyy-MM-dd HH:mm:ss") for a DateTime the OP you wrote mentioned DateTime not Date

PinBack · Accepted Answer · 2017-03-07 09:04:59Z

0

Why do you convert 2 times?

If you have a Date column in SQLite the provider can manged that for you. You can direct insert as DateTime and read as DateTime.

answered Mar 7, 2017 at 9:04

PinBack

2,58415 silver badges18 bronze badges

2 Comments

palm-alchemist Over a year ago

For some reason, reading the date and assigning it to the label directly doesn't allow me format the date using the ".ToString("d")" feature (for lack of a better word. It only works when read in the DataeTime format first.

PinBack Over a year ago

In your solution you have dateDOB (that is propbably a Nullable date). You don't need convert to a string befor insert. And readList["dob"] is a Date (or a Nullable Date if the column is Nullable). Why you don't use DateTime? dateOfBirthEmp = readList["dob"] as DateTime?;

Harry · Accepted Answer · 2023-02-04 06:52:14Z

0

It's feels a bit hacky but this is the only solution I was able to come up with. It just creates a new column, copies all the values in DateTime format to the new column and deletes the old time string column.

DataTable dt = GetDataTable();
string tc = "TimeColumnName";

dt.Constraints.Clear();
int ordinal = dt.Columns[tc].Ordinal;
dt.Columns[tc].ColumnName = "TSOLD";
dt.Columns.Add(tc, typeof(DateTime));
foreach (DataRow row in dt.Rows) row[tc] = Convert.ToDateTime(row["TSOLD"]);
// remove "OLD" column
dt.Columns.Remove("TSOLD");
dt.Columns[tc].SetOrdinal(ordinal);
dt.Constraints.Add(new UniqueConstraint(dt.Columns[tc]));

answered Feb 4, 2023 at 6:52

Harry

1

Collectives™ on Stack Overflow

Reading an SQLite DateTime value from database and assigning it to a C# string variable

4 Answers 4

3 Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related