SQLite3 Date Problem

Forum > Databases

(1/11) > >>

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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---uses db; var i: Integer; begin SQLQuery.SQL.Text := 'SELECT xDate, xNumber from transactions'; DataSource1.DataSet := SQLQuery1; DBGrid1.DataSource := DataSource1; SQLQuery1.Open; for i := 0 to DBGrid1.Columns.Count - 1 do begin if DBGrid1.Columns[i].Field.DataType = ftDate then DBGrid1.Columns[i].DisplayFormat := 'dd/mm/yyyy'; end; //...

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

Navigation

[0] Message Index

[#] Next page