SELECT query with date time

[aijac1314]

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

[aijac1314]

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

[Steve]

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.

[Steve]

help.appsheet.com

Search results for: localization

[aijac1314]

Studying the shared info

[Koichi_Tsuji]

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))

[aijac1314]

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.

[Koichi_Tsuji]

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.