SQLite3 Date Problem

[pat03uk]

I want to write a date to an SQLite3 database table, then retrieve it to a DBGrid column.

I enter the date eg 05/12/2022 as the string 2022-12-05 into Text Field

Using DBBrowser If I enter:
   SELECT  strftime('%d/%m/%Y', xDate), xNumber from transactions, I get the right result ie 05/12/2022.

If I run the query:
   SQLQuery.SQL.Text(SELECT  strftime(''%d/%m/%Y'', xDate), xNumber from transactions)
   DataSource1.DataSet := SQLQuery1;
   DBGrid1.DataSource := DataSource1;
   SQLQuery1.Open;

The date column of the grid contains all blanks.

Why should this be? Am I doing everything correctly?

Pat

[Zvoni]

If DD/MM/YYYY is your local format, then don't use strftime, but use Date-Function

Code: SQL  [Select][+][-]

1. SELECT  DATE(xDate) AS xDate, xNumber FROM transactions

"strftime" returns a String/Text,
"Date(x)" returns a Date

[pat03uk]

Thanks, tried:
    SELECT  DATE(xDate) AS xDate, xNumber FROM transactions

this results in dates as 2022-12-05. (I want 05/12/2022)

If I try:

    SELECT  xDate, xNumber FROM transactions

it results in the same 2022-12-05

[paweld]

You need to define what format you want the column to display directly on the DBGrid

Code: Pascal  [Select][+][-]

1. uses
2.   db;
3.  
4. var
5.   i: Integer;
6. begin
7.   SQLQuery.SQL.Text := 'SELECT xDate, xNumber from transactions';
8.   DataSource1.DataSet := SQLQuery1;
9.   DBGrid1.DataSource := DataSource1;
10.   SQLQuery1.Open;
11.   for i := 0 to DBGrid1.Columns.Count - 1 do
12.   begin
13.     if DBGrid1.Columns[i].Field.DataType = ftDate then
14.       DBGrid1.Columns[i].DisplayFormat := 'dd/mm/yyyy';
15.   end;
16.   //...
17.  

[pat03uk]

Thanks, tried that, no difference.

Bit puzzled, how do I set Field.DataType to ftDate.
DisplayFormat is already set to dd/mm/yyyy in the Object Inspector

Pat

[Handoko]

I am not sure it will help or not. I just want to share how I handle date time data in SQLite.

Code: Pascal  [Select][+][-]

1. const
2.   DateTimeFormat = 'dd-mm-yy hh:nn:ss';
3.  
4. function TimeFormatted(T: TDateTime): string;
5. begin
6.   Result := FormatDateTime(DateTimeFormat, T);
7. end;

The data time data will be stored as REAL data type in SQLite. And each time when showing it to user, I always call TimeFormatted. Because I consistently use DateTimeFormat, my code works. And I prefer to use TStringGrid, because I can format and show the data in whatever way I like.

Note:
I guess you already know, TDateTime actually is Double in Pascal.

[paweld]

Actually, I forgot that “/” is a replacement for the date separator. You need to take the “/” in quotation marks.
Sample in attachment.

[Thaddy]

Handoko provided a better example: store it as TDatetime and convert as necessary.
Because the conversion code has the correct TFormatsettings for the language.

[pat03uk]

I’m storing the date as a string.
My understanding of SQLite is that you can store as string, integer or real - how do you store as TDate or TDatetime?
Pat

[Handoko]

Didn't I already say, TDateTime actually is Double in Pascal?

Storing date data as string (in SQLite) may work too, but you need to extra carefully consistently format the data correctly. Extra work, not worth in my opinion.

[rvk]

I’m storing the date as a string.
My understanding of SQLite is that you can store as string, integer or real - how do you store as TDate or TDatetime?
Pat

Yes, but how did you store it?
What real value (string) holds that field?
If it's not recognized as a date, it can't translate it.

[rvk]

For example

SQLite does not have a dedicated date/time datatype. Instead, date and time values can stored as any of the following:

ISO-8601   A text string that is an ISO 8601 date/time value. Example: '2025-05-29 14:16:00'
Julian day number   The number of days including fractional days since -4713-11-24 12:00:00 Example: 2460825.09444444
Unix timestamp   The number of seconds including fractional seconds since 1970-01-01 00:00:00 Example: 1748528160

https://www.sqlite.org/lang_datefunc.html

So if you saved it as 05/12/2024 then it's not a valid date.
You need to save it as 2024-12-05 as string.

[Handoko]

rvk showed the example, why I said you need to extra carefully consistently format the data correctly.

[pat03uk]

I’m saving the date as a string as 2022-12-05 (see first post)

Pat

[rvk]

Bit puzzled, how do I set Field.DataType to ftDate.
DisplayFormat is already set to dd/mm/yyyy in the Object Inspector

In object inspector?
Did you precreated the fields?
That often goes horribly wrong.

It's best you let fpc create those fields automatically.
Not sure if that's the problem here.

Otherwise you need to create a small example which reproduces the problem.