[sqlite] Checks with dates

[Cecil Westerhof]

2016-04-15 2:29 GMT+02:00 Keith Medcalf :

>
> create table x
> (
>   ds text not null check(date(ds) is not null),
>   de text not null check(date(de) is not null),
>   check(ds < de)
> );
> ??
> ??
> insert into x values ('2016-04-15', '2016-04-13');
> insert into x values ('2016-04-15', '2016-04-17');
> insert into x values ('2016-04-15', '2016-04-32');
>
> The constraint on the column values is that date() is not null (ie, a
> valid date)
>

?Almost, but the dates are allowed to be NULL. But this led me to:
CREATE TABLE x (
ds TEXT CHECK(ds IS NULL OR COALESCE(DATE(ds) == ds, 0)),
de TEXT CHECK(de IS NULL OR COALESCE(DATE(de) == de, 0)),
CHECK(ds IS NULL OR de IS NULL OR ds < de)
)

And this does what I want.

Thanks.

-- 
Cecil Westerhof

[sqlite] Checks with dates

[Cecil Westerhof]

2016-04-14 21:26 GMT+02:00 Richard Hipp :

> On 4/14/16, Cecil Westerhof  wrote:
> > I have a table where I have two fields: toStart and finishBefore. They
> are
> > both dates and when filled the format should be %Y-%m-%d. How can this be
> > checked? 2016-04-31 should not be accepted.
> >
> > The second part is that when both are filled, then finishBefore should be
> > after toStart. Is that possible to check?
>
> Untested:
>
> CHECK( toStart=date(toStart)
> AND finishBefore=date(finishBefore)
> AND finishBefore>toStart )
>

?Does not work, but I have something to play with. ;-)

-- 
Cecil Westerhof

[sqlite] Checks with dates

[Cecil Westerhof]

I have a table where I have two fields: toStart and finishBefore. They are
both dates and when filled the format should be %Y-%m-%d. How can this be
checked? 2016-04-31 should not be accepted.

The second part is that when both are filled, then finishBefore should be
after toStart. Is that possible to check?

-- 
Cecil Westerhof

[sqlite] Checks with dates

[Keith Medcalf]

create table x
(
  ds text not null check(date(ds) is not null),
  de text not null check(date(de) is not null),
  check(ds < de)
);
insert into x values ('2016-04-15', '2016-04-13');
insert into x values ('2016-04-15', '2016-04-17');
insert into x values ('2016-04-15', '2016-04-32');

The constraint on the column values is that date() is not null (ie, a valid 
date)

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
> Sent: Thursday, 14 April, 2016 16:04
> To: SQLite mailing list
> Subject: Re: [sqlite] Checks with dates
> 
> 2016-04-14 21:26 GMT+02:00 Richard Hipp :
> 
> > On 4/14/16, Cecil Westerhof  wrote:
> > > I have a table where I have two fields: toStart and finishBefore. They
> > are
> > > both dates and when filled the format should be %Y-%m-%d. How can this
> be
> > > checked? 2016-04-31 should not be accepted.
> > >
> > > The second part is that when both are filled, then finishBefore should
> be
> > > after toStart. Is that possible to check?
> >
> > Untested:
> >
> > CHECK( toStart=date(toStart)
> > AND finishBefore=date(finishBefore)
> > AND finishBefore>toStart )
> >
> 
> ?Does not work, but I have something to play with. ;-)
> 
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Checks with dates

[Richard Hipp]

On 4/14/16, Cecil Westerhof  wrote:
> I have a table where I have two fields: toStart and finishBefore. They are
> both dates and when filled the format should be %Y-%m-%d. How can this be
> checked? 2016-04-31 should not be accepted.
>
> The second part is that when both are filled, then finishBefore should be
> after toStart. Is that possible to check?

Untested:

CHECK( toStart=date(toStart)
AND finishBefore=date(finishBefore)
AND finishBefore>toStart )
-- 
D. Richard Hipp
drh at sqlite.org