SQLite date problem

[IndianaJones]

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]

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]

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]

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]

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]

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.