For basic level you can use a check constraint
create table Table1 (
TestDate DATETIME
check (TestDate like '____-__-__ __:__:__'
and datetime(TestDate) is not null)
);
That should check that it's in the correct format, and the second part should
check that there's no garbage in there.
Hmm, if TestDate is nullable I think it needs slightly more...
create table Table2 (
TestDate DATETIME
check (TestDate like '____-__-__ __:__:__'
and not (TestDate is not null and datetime(TestDate) is null))
);
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Hick Gunter
Sent: Thursday, November 02, 2017 8:39 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not
consistent
There is no "date" datatype in SQLite, an das you yourself attest, SQLite is
returning exactly whatever was originally inserted.
It is up to your application to define the allowed format for storing datetimes
and to provide conversion between the chosen storage format and the
presentation to the user.
Hint: You could write a user defined function and use that in a check
constraint to enforce your chosen format.
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Rajat Pal
Gesendet: Donnerstag, 02. November 2017 13:24
An: sqlite-users@mailinglists.sqlite.org
Cc: Joy George Chittilapailly <george.chittilapai...@oracle.com>; Minghua Feng
<minghua.f...@oracle.com>; Rohit Udasi <rohit.ud...@oracle.com>
Betreff: [EXTERNAL] [sqlite] SQLite DB - DateTime field values are not
consistent
Hi Team,
We have identified an issue with SQLite database which seems to be a potential
bug.
In SQLite database, All the values of datetime fields are saved as text without
any format check. We can save any text value in the datetime column. This
behavior is creating problem for us because for different regional settings,
datetime values are getting saved in different format.
For example, if the regional format is English(United States), the value is
saved as "2017-09-21 00:00:00" but if the regional format is Finnish(Finland),
the value is saved as "2017-10-27 00.00.00". So some dates have colon(:) as
time separator and some dates have dot (.) as time separator.
Below query can illustrate the problem:
Create Table Table1(TestDate DATETIME);
Insert Into Table1 values (datetime('now','localtime'));
insert into Table1 values ("2017-11-01 17:31:41");
insert into Table1 values ("2017-11-01 17.32.41");
insert into Table1 values ("Testing");
select * from Table1;
Can you please let us know if there is any way we can force the datetime field
to have the datetime values in same format only.
Regards,
Rajat Pal
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
