SQLite date problem
lazarus / 16-Databases

----------------------------------------------------------------------
IndianaJones · 2013-01-12 12:29

Hi forum people,

I have encountered a date problem in SQLite3 which if I create a date
field in the table it restricts the data entry in the DBGrid as YYYY-
MM-DD format.
Can I change this format to DD.MM.YYYY?
Any help would be appreciated, thanks.

e.g. CREATE TABLE Example(x integer primary key, adate date)

----------------------------------------------------------------------
BigChimp · 2013-01-12 15:48

I would think the "problem" lies in the way the datagrid
displays/accepts dates - i.e. the dates are stored as date fields in
your db (as evidenced in your create table snippet) and treated as
dates by FPC/Lazarus.

I would expect the dbgrid date format to follow operating system
current language/locale settings (i.e. date format/currency format)
(note: you haven't specified Lazarus or FPC version or OS, right?).
Following the user interface settings would be a good idea in general,
IMO.

Perhaps there is a way to tell the grid to use a different display
format/validation format for dates... don't know though.

----------------------------------------------------------------------
IndianaJones · 2013-01-12 18:59

Sorry, here is my specifications:
FPC 2.6.1
Lazarus 1.1 SVN 39697
Windows 7
At this point, I would expect the dbgrid date format to follow system
current settings too but it doesnt. I also checked my windows settings
and it says dd.mm.yyyy. Another interesting thing is now I changed the
data format with SQLiteStudio application as dd.mm.yyyy and that data
comes blank in DBGrid.

----------------------------------------------------------------------
BigChimp · 2013-01-13 11:24

1. I wonder whether the dbgrid (really FPC SQLDB) picks up the date
field as a date given your remark about your local settings.
2. "change the date format to dd.mm.yyyy" sounds to me not like
changing the date format but using a varchar/text field with a display
format.
sqlite is of course tricky because apparently it does not do field
type checking (you can put a string in an integer field) as opposed to
other more traditional RDBMS.

Perhaps it's time for a small sample project with included sqlite
db...? Alternatively, perhaps there are others who have more
experience with sqlite who can shed some light here.

----------------------------------------------------------------------
JanRoza · 2013-01-13 14:07

Have you taken a look on the SQLite website
http://www.sqlite.org/lang_datefunc.html
It gives some info about how to manipulate the date in SQL.

It's worth to experiment a bit with this info.

----------------------------------------------------------------------
IndianaJones · 2013-01-14 13:46

I have created a simple example, and everything works fine. DBGrid
gets the data as defined in system environment setting. And in the
database it is sotred as YYYY-MM-DD.
So it seems that the main app. suffer some difficulties and have to
check.
One more thing What is the best way to check date exception? I
attached a simple app.
Thanks.