[RESOLVED] SQLite date field as String to DateTime in DataGridView TextBox

[Mark@SF]

I have a SQLite database and I store dates as ISO 8601 formatted strings ("yyyy-MM-dd HH:mm:ss").

I have bound one of the tables from this database to a DataGridView control (dgvFuel) using a Relation (Vehicle_Fuel).

With bsFuel
    .DataSource = Nothing
    .DataMember = "Vehicle_Fuel"
    .DataSource = bsVehicles
    .Sort = "VehicleID, Odometer, RecDate"
End With
dgvFuel.DataSource = bsFuel

I would like to show the date field values in the grid as DateTime values ("M/d/yyyy"), not string values.

The column that has the date field in the grid is configured as follows:

tbc = New DataGridViewTextBoxColumn
With tbc
    .DataPropertyName = "RecDate"
    .Name = "RecDate"
    .HeaderText = "Date"
    .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
    .DefaultCellStyle.Format = "M/d/yyyy"
    .AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
    .SortMode = DataGridViewColumnSortMode.Automatic
End With
.Columns.Add(tbc)

The DefaultCellStyle.Format property of the DataGridViewTextBoxColumn does not show the date as a DateTime data type.

What do I need to do to show the dates as DateTime values in my DataGridView?

[Zvoni]

Probably better in the .NET-Forum, since it's more to do with .NET

But, why not querying it in the format you want? Meaning: Retrieve it from the SQLite in the Format you want

https://stackoverflow.com/questions/...ormat/36362589

Look at accepted answer: strftime
https://www.sqlite.org/lang_datefunc.html

[Mark@SF]

zvoni -

Retrieve it from the SQLite in the Format you want

Not sure that I understand how to do this. For example, this Select query returns a String data type as the RecDate.

Dim selectData As New SQLite.SQLiteCommand("SELECT AddDate, ModDate, strftime('%m/%d/%Y %H:%M', RecDate) AS RecDate, VehicleID, ProviderID, Odometer, Estimated, Expense, Gallons, MPG, Note FROM [Fuel] ORDER BY Odometer, RecDate", con)

What I want is a DateTime data type.

[Zvoni]

Then you're out of luck with a bound (?) DataGrid, since SQLite has no DateTime-Type

Either retrieve it in the Format you need via SQL, or use an unbound Grid, where you can then Cast the Recordset-Column to DateTime

EDIT: Coming back to your inital post here some weeks ago:
A DateTime has always been a Floating-point number down in the guts where it counts.
In vb6 we used Type Double, SQLite has REAL, in FreePascal the type TDateTime is an Alias for Double (64-Bit floating-point)

EDIT2: Found something
https://forums.xamarin.com/discussio...o-net-datetime

The constructor of SQLConnection takes an argument of storeDateTimeAsTicks which is by default is true make it false

How are you calling the constructor?

[Mark@SF]

zvoni -

Thanks for your help with this question.

I am storing dates as ISO 8601 formatted strings so that they are more human-readable. I realized that for the date field in question, I didn't need the "HH:mm:ss" portion of the date string, so I changed my code to only store dates as "yyyy-MM-dd".