SELECT query with date time

AppSheet AppSheet Q&A
1

Dear all, I need help, I try very hard to figure out the problem, but it is failed.

Below as my table (CashFlow) col:

  1. RECORD ID (KEY)
  2. DATE TIME (date)
  3. DESC (TEXT)

I add a new virtual column in another table which returning a list, below as the as the query:
SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=7))

The query have no issue if the condition of MONTH([DATE TIME]) is 1,2,3,4,5,6,7.

But it failed when the month is 8 and above, and I am sure the records do have for Aug & Sept on ward.

Query for Aug: SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=8))

Thank you

2

Below as sample data in the CashFlow table, column DATE TIME:

31/07/2021
31/07/2021
01/08/2021
29/07/2021
31/07/2021
28/07/2021
28/07/2021
31/07/2021
01/08/2021
04/07/2021
01/08/2021
02/08/2021

3

aijac1314:

SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=7))> > The query have no issue if the condition of MONTH([DATE TIME]) is 1,2,3,4,5,6,7.

I m not sure what you are saying and why it is okey with you for that: -

If you add “<” , it makes sense. But again not sure what your problem and what you are trying to achieve.

SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])

<

=7))

4

Studying the shared info

5

Dear Tsuji, thanks for the advice.

Actually I am grouping the data by month, so I do have multi query as below:
SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=1))
SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=2))
SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=3))
SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=4))
SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=5))
SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=6))
SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=7))

All above query does do their job, but advance of July, the query didn’t return any data although there are data for Aug onward. Below is the failed query:
SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=8))
SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,MONTH([DATE TIME])=9))

Anywhere, thank you very much.

6

My first impression is your expression is inefficient (sorry).

One single expression would do the same.

SELECT(CashFlow[record id],AND(YEAR([DATE TIME])=2021,And(MONTH([DATE TIME])<=1),MONTH([DATE TIME])<=7)))

Still dont understand what your goal is going to be.

7

aijac1314:

Below as sample data in the CashFlow table, column DATE TIME:> > 31/07/2021> 31/07/2021> 01/08/2021

Your example dates are in a non-US format. In order for AppSheet to correctly interpret those dates, both your spreadsheet and AppSheet must be properly configured with the correct locales.

2 Likes