Re: [sqlite] SELECT with CASE

[Csányi Pál]

2018-04-12 21:09 GMT+02:00 Csányi Pál :
> Thank you very much for the help and for the explanations.
>
> Waw! It is so complicated at first! I hope I shall understand these soon.
>
> Finally I decide to use this query:
> SELECT Keltezes FROM Orak WHERE Keltezes >= date('now','localtime')
> ORDER BY Keltezes LIMIT 1;
>
> At last this works on my Gentoo linux system here, on my laptop.
> I used datefudge to give a fake date to the sqlite3.
> When I run the above mentioned query without 'localtime', it gives the
> right date!
> But, when I run the same query with 'localtime', then I get the localtime.
>
> Because I think an Android operating system also uses localtime, I am
> going to try out this query now.

Well, it not works on my phone, altough I apply the 'localtime' in the query.
When I start the app it shows the TOMORROW date out there.
Can I ask you here what could be the solution?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Thank you very much for the help and for the explanations.

Waw! It is so complicated at first! I hope I shall understand these soon.

Finally I decide to use this query:
SELECT Keltezes FROM Orak WHERE Keltezes >= date('now','localtime')
ORDER BY Keltezes LIMIT 1;

At last this works on my Gentoo linux system here, on my laptop.
I used datefudge to give a fake date to the sqlite3.
When I run the above mentioned query without 'localtime', it gives the
right date!
But, when I run the same query with 'localtime', then I get the localtime.

Because I think an Android operating system also uses localtime, I am
going to try out this query now.

2018-04-12 18:48 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>
> The Query Planner should decide in the case of the MIN containing query that 
> the best solution is to traverse TheDate in order and return the first hit.  
> This may entail the creation of the necessary index if it does not exist and 
> so the two plans should be more or less identical.
>
> However, if used in a subquery, the inclusion of the LIMIT may preclude 
> flattening whereas the MIN function version will not preclude flattening.  
> Since the most likely alternative to flattening is a co-routine it probably 
> would not make much of a difference.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>>Sent: Thursday, 12 April, 2018 10:24
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>On 12 Apr 2018, at 5:16pm, R Smith <ryansmit...@gmail.com> wrote:
>>
>>> SELECT MIN(TheDate) -- get the smallest date
>>>   FROM Orak -- from the table with School-days
>>>  WHERE TheDate >= date('now')   -- where the school-day is later or
>>equal to today.
>>> ;
>>
>>This reflects exactly the right structure for the data.  The
>>following may be a little faster:
>>
>>  SELECT TheDate
>>FROM Orak
>>   WHERE TheDate >= date('now')
>>ORDER BY TheDate
>>   LIMIT 1
>>
>>Both the above queries will perform far faster if there is an index
>>on the "TheDate" column in Orak.  I'm not quite sure how your data is
>>organised but this may perform another job too if it is a UNIQUE
>>index.
>>
>>Simon.
>>___
>>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

Re: [sqlite] SELECT with CASE

[Keith Medcalf]

The Query Planner should decide in the case of the MIN containing query that 
the best solution is to traverse TheDate in order and return the first hit.  
This may entail the creation of the necessary index if it does not exist and so 
the two plans should be more or less identical.

However, if used in a subquery, the inclusion of the LIMIT may preclude 
flattening whereas the MIN function version will not preclude flattening.  
Since the most likely alternative to flattening is a co-routine it probably 
would not make much of a difference.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Thursday, 12 April, 2018 10:24
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>On 12 Apr 2018, at 5:16pm, R Smith <ryansmit...@gmail.com> wrote:
>
>> SELECT MIN(TheDate) -- get the smallest date
>>   FROM Orak -- from the table with School-days
>>  WHERE TheDate >= date('now')   -- where the school-day is later or
>equal to today.
>> ;
>
>This reflects exactly the right structure for the data.  The
>following may be a little faster:
>
>  SELECT TheDate
>FROM Orak
>   WHERE TheDate >= date('now')
>ORDER BY TheDate
>   LIMIT 1
>
>Both the above queries will perform far faster if there is an index
>on the "TheDate" column in Orak.  I'm not quite sure how your data is
>organised but this may perform another job too if it is a UNIQUE
>index.
>
>Simon.
>___
>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

Re: [sqlite] SELECT with CASE

[Simon Slavin]

On 12 Apr 2018, at 5:16pm, R Smith  wrote:

> SELECT MIN(TheDate) -- get the smallest date
>   FROM Orak -- from the table with School-days
>  WHERE TheDate >= date('now')   -- where the school-day is later or equal to 
> today.
> ;

This reflects exactly the right structure for the data.  The following may be a 
little faster:

  SELECT TheDate
FROM Orak
   WHERE TheDate >= date('now')
ORDER BY TheDate
   LIMIT 1

Both the above queries will perform far faster if there is an index on the 
"TheDate" column in Orak.  I'm not quite sure how your data is organised but 
this may perform another job too if it is a UNIQUE index.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[R Smith]

You're right.
I am developing an Android app on App Inventor2.
The app is in Hungarian language so the SQLite database contains
tables and columns with Hungarian names.

The whole schema is like this:
CREATE TABLE Beiratkozottak(
   az INTEGER PRIMARY KEY UNIQUE,
   TanuloNeve TEXT NOT NULL,
   EvFolyam INTEGER NOT NULL,
   Tagozat TEXT NOT NULL
);
CREATE TABLE Hianyzok(
   az INTEGER PRIMARY KEY,
   HianyzoTanulo TEXT NOT NULL,
   Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE JelenLevok(
   az INTEGER PRIMARY KEY UNIQUE,
   JelenLevoTanulo TEXT NOT NULL,
   Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE Orak(
   az INTEGER PRIMARY KEY UNIQUE,
   Keltezes DATE DEFAULT NULL,
   OrarendNapja TEXT DEFAULT NULL,
   BlokkoraSzama TEXT DEFAULT NULL,
   EvFolyam INTEGER DEFAULT NULL,
   Tagozat TEXT DEFAULT NULL,
   OraVazlat TEXT DEFAULT NULL,
   OraNaplo TEXT DEFAULT NULL,
   Emlekezteto TEXT DEFAULT NULL,
   OraRend INTEGER REFERENCES OraRend(az) DEFAULT NULL,
   Beiratkozottak INTEGER REFERENCES Beiratkozottak(az) DEFAULT NULL,
   TanSzombatokOraRendjei INTEGER REFERENCES TanSzombatokOraRendjei(az)
DEFAULT NULL,
   KivetelesNapokOraRendjei INTEGER REFERENCES
KivetelesNapokOraRendjei(az) DEFAULT NULL
);
CREATE TABLE OraRend(
   az INTEGER PRIMARY KEY UNIQUE,
   aHetNapja TEXT,
   HanyadikIskolaOra INTEGER,
   EvFolyam INTEGER,
   Tagozat TEXT
);
CREATE TABLE TanSzombatokOraRendjei(
   az INTEGER PRIMARY KEY UNIQUE,
   Keltezes DATE DEFAULT NULL,
   aHetNapja TEXT
);
CREATE TABLE KivetelesNapokOraRendjei(
   az INTEGER PRIMARY KEY UNIQUE,
   Keltezes DATE DEFAULT NULL,
   aHetNapja TEXT
);

There are datas already in this database.
The Orak table contains dates on which we have school, that is teaching.
That mean not every day in the year is a school day.
The OraRend table contains the timetable.

So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.


Ah, good we checked.

What guarantee is there then that "tomorrow" will be a valid school day 
if today isn't?  What if today is Saturday, and we don't have school on 
Saturday, then tomorrow is Sunday, which also might not be a school day?


To solve this puzzle, let's start by stating the problem in English:
"Give me the date for TODAY if TODAY is a valid school day, else give me 
the date for the next possible valid school day."


Now let's translate that in terms of tables and records in a database:
"Give me the date for TODAY if any record exists in my table of 
school-days for TODAY, else give me the first date (i.e. smallest date) 
from a record that exists in the schooldays table AFTER (i.e. 
later-than) today.


now let's say that in SQL:

SELECT MIN(TheDate)             -- get the smallest date
  FROM Orak                     -- from the table with School-days
 WHERE TheDate >= date('now')   -- where the school-day is later or 
equal to today.

;

That should show the real date you need, in all cases, but assuming the 
table has more available school days than today. (If nothing more 
exists, nothing more can be shown).


Good luck!
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[David Raymond]

The CASE method you were using was going through the Dates table and returning 
1 result . Since you're looking for only 1 return row 
from an entire table, then you might want something different.

select case exists (select 1 from Dates where TheDate = date('now'))
  when 1 then date('now')
  else date('now', '+1 day')
  end as TheDate;

Or if you need the next day which isn't in the table:

with recursive foo (tempDate) as (
values (date('now'))
union all
select date(tempDate, '+1 day') from foo
where exists (select 1 from Dates where TheDate = tempDate)
)
select max(tempDate) as TheDate from foo;



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Csányi Pál
Sent: Thursday, April 12, 2018 11:36 AM
To: SQLite mailing list
Subject: Re: [sqlite] SELECT with CASE

Thank you very much!

Just can't understand why the CASE method does not work?
It can't be done with the CASE expression at all?

2018-04-12 17:26 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>
> Then Richard is correct (of course) ... which is a perfect translation of the 
> problem statement into SQL.
>
> SELECT COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')), 
> date('now','+1 day')) as TheDate;
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:20
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>Yes, this is what I am asking.
>>
>>2018-04-12 17:17 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>
>>> The question you asked was:
>>>
>>> "Then how can I get only that date from the Dates table - which is
>>> equal to the current date?"
>>>
>>> and you are now posing a second question:
>>>
>>> ">Yes, but I want the CASE because if there is no such date in the
>>>>Dates
>>>>table which is equal to the date('now') then it should return the
>>>>date('now','+1 day')."
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>>
>>> "I have a table with a bunch-o-dates in it.  I want a query which
>>will return, at the time the query is run, based on the comuter on
>>which the query is run concept of today's date, today's date, if that
>>date is in the table otherwise the tomorrow's date (based on the
>>current concept of 'tomorrow' on the computer on which the query is
>>run."
>>>
>>> Is this what you are asking?
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to
>>Heaven says a lot about anticipated traffic volume.
>>>
>>>
>>>>-Original Message-
>>>>From: sqlite-users [mailto:sqlite-users-
>>>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>>>Sent: Thursday, 12 April, 2018 09:10
>>>>To: SQLite mailing list
>>>>Subject: Re: [sqlite] SELECT with CASE
>>>>
>>>>2018-04-12 17:08 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>>>
>>>>> select TheDate from Dates where TheDate == date('now');
>>>>
>>>>Yes, but I want the CASE because if there is no such date in the
>>>>Dates
>>>>table which is equal to the date('now') then it should return the
>>>>date('now','+1 day').
>>>>___
>>>>sqlite-users mailing list
>>>>sqlite-users@mailinglists.sqlite.org
>>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
___
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

Re: [sqlite] SELECT with CASE

[Keith Medcalf]

Yes.  An alternate solution might be to return the "smallest" date (assuming 
that today occurs before (is less than) tomorrow -- currently the case, but who 
knows if it will always be so ...)

SELECT MIN(CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1 day') 
END) as TheDate FROM Dates;

Of course, this requires that there be at least one row in the table and will 
return nothing if there are no rows in Dates at all.  Thus the problem 
statement becomes "Return todays date if it is in the table, if not return 
tomorrow's date, unless there are no dates in the table at all in which case 
return nothing".  This is not the case as the original problem statement.

It will also be somewhat (perhaps a lot) more inefficient, particularly if 
there are more than a trivial number of rows in your Dates table.

You can also implement COALESCE using case:

SELECT CASE WHEN (SELECT TheDate FROM Dates WHERE TheDate==date('now')) IS NOT 
NULL
THEN (SELECT TheDate FROM Dates WHERE TheDate==date('now))
ELSE date('now', '+1 day')
   END as TheDate;

However, it is not as efficient as using COALESCE, and is about two times more 
inefficient than the first example since now the Dates table has to be scanned 
twice.  It is, however, compliant with the original problem definition.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:36
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>Thank you very much!
>
>Just can't understand why the CASE method does not work?
>It can't be done with the CASE expression at all?
>
>2018-04-12 17:26 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>
>> Then Richard is correct (of course) ... which is a perfect
>translation of the problem statement into SQL.
>>
>> SELECT COALESCE((SELECT thedate FROM dates WHERE
>thedate==date('now')), date('now','+1 day')) as TheDate;
>>
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says a lot about anticipated traffic volume.
>>
>>
>>>-Original Message-
>>>From: sqlite-users [mailto:sqlite-users-
>>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>>Sent: Thursday, 12 April, 2018 09:20
>>>To: SQLite mailing list
>>>Subject: Re: [sqlite] SELECT with CASE
>>>
>>>Yes, this is what I am asking.
>>>
>>>2018-04-12 17:17 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>>
>>>> The question you asked was:
>>>>
>>>> "Then how can I get only that date from the Dates table - which
>is
>>>> equal to the current date?"
>>>>
>>>> and you are now posing a second question:
>>>>
>>>> ">Yes, but I want the CASE because if there is no such date in
>the
>>>>>Dates
>>>>>table which is equal to the date('now') then it should return the
>>>>>date('now','+1 day')."
>>>>
>>>> Which seems like a rather long winded way of stating the problem:
>>>>
>>>> "I have a table with a bunch-o-dates in it.  I want a query which
>>>will return, at the time the query is run, based on the comuter on
>>>which the query is run concept of today's date, today's date, if
>that
>>>date is in the table otherwise the tomorrow's date (based on the
>>>current concept of 'tomorrow' on the computer on which the query is
>>>run."
>>>>
>>>> Is this what you are asking?
>>>>
>>>> ---
>>>> The fact that there's a Highway to Hell but only a Stairway to
>>>Heaven says a lot about anticipated traffic volume.
>>>>
>>>>
>>>>>-Original Message-
>>>>>From: sqlite-users [mailto:sqlite-users-
>>>>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>>>>Sent: Thursday, 12 April, 2018 09:10
>>>>>To: SQLite mailing list
>>>>>Subject: Re: [sqlite] SELECT with CASE
>>>>>
>>>>>2018-04-12 17:08 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>>>>
>>>>>> select TheDate from Dates where TheDate == date('now');
>>>>>
>>>>>Yes, but I want the CASE because if there is no such date in the
>>>>>Dates
>>>>>table which is equal to the date('now') then it should return the
>>>>>date('now','+1 day').
>>>>>___
>>>>>sqlite-users mailing list
>>>>>sqlite-users@mailinglists.sqlite.org
>>>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>___
>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

Re: [sqlite] SELECT with CASE

[Peter Da Silva]

Maybe something like: SELECT MIN(thedate) FROM dates WHERE thedate >= 
date('now');

On 4/12/18, 11:05 AM, "sqlite-users on behalf of Peter Da Silva" 
 wrote:

Ah, so if there's two days in a row that aren't school days, you need to be 
able to select a day two or more days in the future.

On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:
So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
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


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Peter Da Silva]

Ah, so if there's two days in a row that aren't school days, you need to be 
able to select a day two or more days in the future.

On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:
So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
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

Re: [sqlite] SELECT with CASE

[R Smith]

On 2018/04/12 5:35 PM, Csányi Pál wrote:

Thank you very much!

Just can't understand why the CASE method does not work?
It can't be done with the CASE expression at all?


The CASE expression modifies a single line, the WHERE clause restricts 
the selection to the lines that qualify.


So if you decide to do it in a CASE expression, your case expression 
worked just fine.


Your sql was (expanded a little for legibility):

SELECT
CASE TheDate
  WHEN date('now') THEN TheDate
  ELSE date('now','+1 day')
END TheDate
  FROM Dates
;


Which, when translated to plain English says:

a. For every record in table"Dates", show me a value called "TheDate" 
which is decided upon as follows:
  b. When the value in column "TheDate" is equal to today's date (for 
what my current computer thinks is "today" locally - let's call this 
TODAY), then simply put THAT TheDate value,

  c. Else put tomorrow's date (let's call this TOMORROW).

Now some things you can see from this:
- Because of a. - you will always see ALL rows listed.
- In b. the selection is superfluous. The only value that can ever be 
given for TheDate is TODAY.   [If TheDate==TODAY, then show TheDate 
(i.e. TODAY)]
- in c. the selection can only ever be TOMORROW, nothing else. For you 
specify that if NOT (TheDate == TODAY) - i.e. the ELSE, then show 
TOMORROW. Always.


This means the only two possible dates that can be the result of your 
CASE statement is either TODAY, or TOMORROW. Nothing else. And indeed, 
in the output that you sent, it is clearly the case, you have lots of 
lines showing TOMORROW (obviously for the entries in your table where 
the "TheDate" column was NOT equal to TODAY), and one line showing TODAY 
(obviously for the one entry where the "TheDate" column was indeed equal 
to TODAY).


What you then later asked is that you do not wish to see any of this, 
you want to know whether there exists a date such as TODAY in the table 
at all... If so, you want one single answer showing that date (i.e. 
TODAY), else you would like the one single answer to say TOMORROW. This 
absolutely /HAS/ to be filtered out using a WHERE clause. There is no 
way CASE can limit the shown rows - it only selects based upon a value 
in the current row.


Does that answer the CASE question? If not, feel free to ask again with 
maybe examples of how you expect it to work, which will help us to know 
what misconception to assist with.


Good luck!
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Hi Ryan,

2018-04-12 17:36 GMT+02:00 R Smith :
> On 2018/04/12 5:20 PM, Csányi Pál wrote:
>>
>> Yes, this is what I am asking.
>>
>> 2018-04-12 17:17 GMT+02:00 Keith Medcalf :
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>> "I have a table with a bunch-o-dates in it.  I want a query which will
>>> return, at the time the query is run, based on the comuter on which the
>>> query is run concept of today's date, today's date, if that date is in the
>>> table otherwise the tomorrow's date (based on the current concept of
>>> 'tomorrow' on the computer on which the query is run."
>>>
>>> Is this what you are asking?
>
>
> Ok, now that we know what you are asking, there are some ways of doing it
> easily, of which Richard's way will work perfectly.
>
> But, some more information will be useful:
> - Can there be dates later than today in the table or not?
> - Can there be multiple dates for today in the table, or just the one?
> - Do you need to run this query often, or is it used simply to determine the
> next date available for another query (insert perhaps)?
>
> I'm asking because I feel like the query you are asking for is achieving
> something as part of a larger query or group of functions that may all be
> made simpler. I could of course be wrong, but if you'd like to find out,
> post the whole schema and method you are trying to make and we could suggest
> what might work the fastest/easiest/best - or we might at least confirm that
> you are already doing it the best way.
>
> Cheers!
> Ryan

> - Can there be dates later than today in the table or not?
Yes, it can.

> - Can there be multiple dates for today in the table, or just the one?
Yes, it can.

> - Do you need to run this query often, or is it used simply to determine the
> next date available for another query (insert perhaps)?
It is used simply to determine the next date available for another query.

> I'm asking because I feel like the query you are asking for is achieving
> something as part of a larger query or group of functions that may all be
> made simpler. I could of course be wrong, but if you'd like to find out,
> post the whole schema and method you are trying to make and we could suggest
> what might work the fastest/easiest/best - or we might at least confirm that
> you are already doing it the best way.

You're right.
I am developing an Android app on App Inventor2.
The app is in Hungarian language so the SQLite database contains
tables and columns with Hungarian names.

The whole schema is like this:
CREATE TABLE Beiratkozottak(
  az INTEGER PRIMARY KEY UNIQUE,
  TanuloNeve TEXT NOT NULL,
  EvFolyam INTEGER NOT NULL,
  Tagozat TEXT NOT NULL
);
CREATE TABLE Hianyzok(
  az INTEGER PRIMARY KEY,
  HianyzoTanulo TEXT NOT NULL,
  Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE JelenLevok(
  az INTEGER PRIMARY KEY UNIQUE,
  JelenLevoTanulo TEXT NOT NULL,
  Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE Orak(
  az INTEGER PRIMARY KEY UNIQUE,
  Keltezes DATE DEFAULT NULL,
  OrarendNapja TEXT DEFAULT NULL,
  BlokkoraSzama TEXT DEFAULT NULL,
  EvFolyam INTEGER DEFAULT NULL,
  Tagozat TEXT DEFAULT NULL,
  OraVazlat TEXT DEFAULT NULL,
  OraNaplo TEXT DEFAULT NULL,
  Emlekezteto TEXT DEFAULT NULL,
  OraRend INTEGER REFERENCES OraRend(az) DEFAULT NULL,
  Beiratkozottak INTEGER REFERENCES Beiratkozottak(az) DEFAULT NULL,
  TanSzombatokOraRendjei INTEGER REFERENCES TanSzombatokOraRendjei(az)
DEFAULT NULL,
  KivetelesNapokOraRendjei INTEGER REFERENCES
KivetelesNapokOraRendjei(az) DEFAULT NULL
);
CREATE TABLE OraRend(
  az INTEGER PRIMARY KEY UNIQUE,
  aHetNapja TEXT,
  HanyadikIskolaOra INTEGER,
  EvFolyam INTEGER,
  Tagozat TEXT
);
CREATE TABLE TanSzombatokOraRendjei(
  az INTEGER PRIMARY KEY UNIQUE,
  Keltezes DATE DEFAULT NULL,
  aHetNapja TEXT
);
CREATE TABLE KivetelesNapokOraRendjei(
  az INTEGER PRIMARY KEY UNIQUE,
  Keltezes DATE DEFAULT NULL,
  aHetNapja TEXT
);

There are datas already in this database.
The Orak table contains dates on which we have school, that is teaching.
That mean not every day in the year is a school day.
The OraRend table contains the timetable.

So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Keith Medcalf]

Assuming, of course, that all the dates are in UT1 (UTC/GMT/Zulu) and not the 
computers' (running the query) current concept of 'local wall clock time'.  

If your table of Dates are not UT1 then you need to add the 'localtime' 
modifier to the datetime function calls and pray that the computer has the 
correct "rules" for the translation from UT1/GMT/Zulu to your desired 
'localtime'.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>Sent: Thursday, 12 April, 2018 09:26
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>
>Then Richard is correct (of course) ... which is a perfect
>translation of the problem statement into SQL.
>
>SELECT COALESCE((SELECT thedate FROM dates WHERE
>thedate==date('now')), date('now','+1 day')) as TheDate;
>
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:20
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>Yes, this is what I am asking.
>>
>>2018-04-12 17:17 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>
>>> The question you asked was:
>>>
>>> "Then how can I get only that date from the Dates table - which is
>>> equal to the current date?"
>>>
>>> and you are now posing a second question:
>>>
>>> ">Yes, but I want the CASE because if there is no such date in the
>>>>Dates
>>>>table which is equal to the date('now') then it should return the
>>>>date('now','+1 day')."
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>>
>>> "I have a table with a bunch-o-dates in it.  I want a query which
>>will return, at the time the query is run, based on the comuter on
>>which the query is run concept of today's date, today's date, if
>that
>>date is in the table otherwise the tomorrow's date (based on the
>>current concept of 'tomorrow' on the computer on which the query is
>>run."
>>>
>>> Is this what you are asking?
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to
>>Heaven says a lot about anticipated traffic volume.
>>>
>>>
>>>>-Original Message-
>>>>From: sqlite-users [mailto:sqlite-users-
>>>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>>>Sent: Thursday, 12 April, 2018 09:10
>>>>To: SQLite mailing list
>>>>Subject: Re: [sqlite] SELECT with CASE
>>>>
>>>>2018-04-12 17:08 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>>>
>>>>> select TheDate from Dates where TheDate == date('now');
>>>>
>>>>Yes, but I want the CASE because if there is no such date in the
>>>>Dates
>>>>table which is equal to the date('now') then it should return the
>>>>date('now','+1 day').
>>>>___
>>>>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
>>___
>>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



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[R Smith]

On 2018/04/12 5:20 PM, Csányi Pál wrote:

Yes, this is what I am asking.

2018-04-12 17:17 GMT+02:00 Keith Medcalf :

Which seems like a rather long winded way of stating the problem:
"I have a table with a bunch-o-dates in it.  I want a query which will return, at 
the time the query is run, based on the comuter on which the query is run concept of 
today's date, today's date, if that date is in the table otherwise the tomorrow's date 
(based on the current concept of 'tomorrow' on the computer on which the query is 
run."

Is this what you are asking?


Ok, now that we know what you are asking, there are some ways of doing 
it easily, of which Richard's way will work perfectly.


But, some more information will be useful:
- Can there be dates later than today in the table or not?
- Can there be multiple dates for today in the table, or just the one?
- Do you need to run this query often, or is it used simply to determine 
the next date available for another query (insert perhaps)?


I'm asking because I feel like the query you are asking for is achieving 
something as part of a larger query or group of functions that may all 
be made simpler. I could of course be wrong, but if you'd like to find 
out, post the whole schema and method you are trying to make and we 
could suggest what might work the fastest/easiest/best - or we might at 
least confirm that you are already doing it the best way.


Cheers!
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Thank you very much!

Just can't understand why the CASE method does not work?
It can't be done with the CASE expression at all?

2018-04-12 17:26 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>
> Then Richard is correct (of course) ... which is a perfect translation of the 
> problem statement into SQL.
>
> SELECT COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')), 
> date('now','+1 day')) as TheDate;
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:20
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>Yes, this is what I am asking.
>>
>>2018-04-12 17:17 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>
>>> The question you asked was:
>>>
>>> "Then how can I get only that date from the Dates table - which is
>>> equal to the current date?"
>>>
>>> and you are now posing a second question:
>>>
>>> ">Yes, but I want the CASE because if there is no such date in the
>>>>Dates
>>>>table which is equal to the date('now') then it should return the
>>>>date('now','+1 day')."
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>>
>>> "I have a table with a bunch-o-dates in it.  I want a query which
>>will return, at the time the query is run, based on the comuter on
>>which the query is run concept of today's date, today's date, if that
>>date is in the table otherwise the tomorrow's date (based on the
>>current concept of 'tomorrow' on the computer on which the query is
>>run."
>>>
>>> Is this what you are asking?
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to
>>Heaven says a lot about anticipated traffic volume.
>>>
>>>
>>>>-Original Message-
>>>>From: sqlite-users [mailto:sqlite-users-
>>>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>>>Sent: Thursday, 12 April, 2018 09:10
>>>>To: SQLite mailing list
>>>>Subject: Re: [sqlite] SELECT with CASE
>>>>
>>>>2018-04-12 17:08 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>>>
>>>>> select TheDate from Dates where TheDate == date('now');
>>>>
>>>>Yes, but I want the CASE because if there is no such date in the
>>>>Dates
>>>>table which is equal to the date('now') then it should return the
>>>>date('now','+1 day').
>>>>___
>>>>sqlite-users mailing list
>>>>sqlite-users@mailinglists.sqlite.org
>>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Keith Medcalf]

Then Richard is correct (of course) ... which is a perfect translation of the 
problem statement into SQL.

SELECT COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')), 
date('now','+1 day')) as TheDate;


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:20
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>Yes, this is what I am asking.
>
>2018-04-12 17:17 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>
>> The question you asked was:
>>
>> "Then how can I get only that date from the Dates table - which is
>> equal to the current date?"
>>
>> and you are now posing a second question:
>>
>> ">Yes, but I want the CASE because if there is no such date in the
>>>Dates
>>>table which is equal to the date('now') then it should return the
>>>date('now','+1 day')."
>>
>> Which seems like a rather long winded way of stating the problem:
>>
>> "I have a table with a bunch-o-dates in it.  I want a query which
>will return, at the time the query is run, based on the comuter on
>which the query is run concept of today's date, today's date, if that
>date is in the table otherwise the tomorrow's date (based on the
>current concept of 'tomorrow' on the computer on which the query is
>run."
>>
>> Is this what you are asking?
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says a lot about anticipated traffic volume.
>>
>>
>>>-Original Message-
>>>From: sqlite-users [mailto:sqlite-users-
>>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>>Sent: Thursday, 12 April, 2018 09:10
>>>To: SQLite mailing list
>>>Subject: Re: [sqlite] SELECT with CASE
>>>
>>>2018-04-12 17:08 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>>
>>>> select TheDate from Dates where TheDate == date('now');
>>>
>>>Yes, but I want the CASE because if there is no such date in the
>>>Dates
>>>table which is equal to the date('now') then it should return the
>>>date('now','+1 day').
>>>___
>>>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
>___
>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

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Yes, this is what I am asking.

2018-04-12 17:17 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>
> The question you asked was:
>
> "Then how can I get only that date from the Dates table - which is
> equal to the current date?"
>
> and you are now posing a second question:
>
> ">Yes, but I want the CASE because if there is no such date in the
>>Dates
>>table which is equal to the date('now') then it should return the
>>date('now','+1 day')."
>
> Which seems like a rather long winded way of stating the problem:
>
> "I have a table with a bunch-o-dates in it.  I want a query which will 
> return, at the time the query is run, based on the comuter on which the query 
> is run concept of today's date, today's date, if that date is in the table 
> otherwise the tomorrow's date (based on the current concept of 'tomorrow' on 
> the computer on which the query is run."
>
> Is this what you are asking?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:10
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>2018-04-12 17:08 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>
>>> select TheDate from Dates where TheDate == date('now');
>>
>>Yes, but I want the CASE because if there is no such date in the
>>Dates
>>table which is equal to the date('now') then it should return the
>>date('now','+1 day').
>>___
>>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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Keith Medcalf]

The question you asked was: 

"Then how can I get only that date from the Dates table - which is
equal to the current date?"

and you are now posing a second question:

">Yes, but I want the CASE because if there is no such date in the
>Dates
>table which is equal to the date('now') then it should return the
>date('now','+1 day')."

Which seems like a rather long winded way of stating the problem:

"I have a table with a bunch-o-dates in it.  I want a query which will return, 
at the time the query is run, based on the comuter on which the query is run 
concept of today's date, today's date, if that date is in the table otherwise 
the tomorrow's date (based on the current concept of 'tomorrow' on the computer 
on which the query is run."

Is this what you are asking?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:10
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>2018-04-12 17:08 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>
>> select TheDate from Dates where TheDate == date('now');
>
>Yes, but I want the CASE because if there is no such date in the
>Dates
>table which is equal to the date('now') then it should return the
>date('now','+1 day').
>___
>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

Re: [sqlite] SELECT with CASE

[Richard Hipp]

On 4/12/18, Csányi Pál  wrote:
> 2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>>
>> select TheDate from Dates where TheDate == date('now');
>
> Yes, but I want the CASE because if there is no such date in the Dates
> table which is equal to the date('now') then it should return the
> date('now','+1 day').

... COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')),
date('now','+1 day'))

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>
> select TheDate from Dates where TheDate == date('now');

Yes, but I want the CASE because if there is no such date in the Dates
table which is equal to the date('now') then it should return the
date('now','+1 day').
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Keith Medcalf]

select TheDate from Dates where TheDate == date('now');

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:06
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>2018-04-12 17:00 GMT+02:00 Peter Da Silva
><peter.dasi...@flightaware.com>:
>> One of the lines of the output does indeed have '2018-04-12' as
>expected.
>
>Indeed, I did not notice.
>Then how can I get only that date from the Dates table - which is
>equal to the current date?
>___
>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

Re: [sqlite] SELECT with CASE

[Csányi Pál]

2018-04-12 17:00 GMT+02:00 Peter Da Silva :
> One of the lines of the output does indeed have '2018-04-12' as expected.

Indeed, I did not notice.
Then how can I get only that date from the Dates table - which is
equal to the current date?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Peter Da Silva]

One of the lines of the output does indeed have '2018-04-12' as expected.

On 4/12/18, 9:59 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:

2018-04-12 16:51 GMT+02:00 Peter Da Silva :
> You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which 
is what you're getting, no?

Yes, indeed.
But I thought the first part would be done:
CASE TheDate WHEN date('now') THEN TheDate

that is, if the TheDate is = date('now') THEN
it should select that TheDate which is equal to the current date:
date('now'), no?
___
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

Re: [sqlite] SELECT with CASE

[Csányi Pál]

2018-04-12 16:51 GMT+02:00 Peter Da Silva :
> You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is 
> what you're getting, no?

Yes, indeed.
But I thought the first part would be done:
CASE TheDate WHEN date('now') THEN TheDate

that is, if the TheDate is = date('now') THEN
it should select that TheDate which is equal to the current date:
date('now'), no?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Peter Da Silva]

You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is what 
you're getting, no?

On 4/12/18, 9:47 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:

Hi Simon,

2018-04-12 14:32 GMT+02:00 Simon Slavin :
> On 12 Apr 2018, at 1:25pm, Csányi Pál  wrote:
>
>> SELECT CASE TheDate = date('now') WHEN TheDate ...
>
> I don't think that's what you wanted.  Perhaps
>
> SELECT CASE TheDate WHEN date('now') ...
>
> But you should test the output of "date('now')" to make sure it is in the 
format you want.

The output of "date('now') is in format I want:

SELECT date('now');
2018-04-12

I tried this:

SELECT CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1
day') END TheDate FROM Dates;
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-12
2018-04-13
2018-04-13
2018-04-13

but I expect the output like this:
2018-04-12

because today date is:
2018-04-12

What am I missing here?

-- 
Best, Pali
___
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

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Hi Simon,

2018-04-12 14:32 GMT+02:00 Simon Slavin :
> On 12 Apr 2018, at 1:25pm, Csányi Pál  wrote:
>
>> SELECT CASE TheDate = date('now') WHEN TheDate ...
>
> I don't think that's what you wanted.  Perhaps
>
> SELECT CASE TheDate WHEN date('now') ...
>
> But you should test the output of "date('now')" to make sure it is in the 
> format you want.

The output of "date('now') is in format I want:

SELECT date('now');
2018-04-12

I tried this:

SELECT CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1
day') END TheDate FROM Dates;
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-12
2018-04-13
2018-04-13
2018-04-13

but I expect the output like this:
2018-04-12

because today date is:
2018-04-12

What am I missing here?

-- 
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Simon Slavin]

On 12 Apr 2018, at 1:25pm, Csányi Pál  wrote:

> SELECT CASE TheDate = date('now') WHEN TheDate ...

I don't think that's what you wanted.  Perhaps

SELECT CASE TheDate WHEN date('now') ...

But you should test the output of "date('now')" to make sure it is in the 
format you want.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users