I have this table:
| ID | DOC_ID | DATE | AMOUNT | PAID_AMOUNT | NET_BALANCE | INS_DATE | MOD_DATE | INDEX_ID | STATUS | TRANSPORT | TRANS_DOC_ID | RETURNED | DATES | |----|----------|------------|--------|-------------|-------------|---------------------|---------------------|----------|-----------|-----------|--------------|----------|-------| | 30 | 41514635 | 2020-01-01 | 188.88 | 0 | 88.88 | 2021-01-01 20:57:44 | 2021-01-01 20:57:44 | 1 | PENDIENTE | N/A | | 0 | | | 30 | 41514635 | 2021-01-01 | 88.88 | 0 | 88.88 | 2021-01-01 20:57:44 | 2021-01-01 20:57:44 | 2 | PENDIENTE | N/A | | 0 | | | 30 | 41514635 | 2020-03-12 | 288.88 | 0 | 88.88 | 2021-01-01 20:57:44 | 2021-01-01 20:57:44 | 3 | PENDIENTE | N/A | | 0 | | | 30 | 41514635 | 2020-06-30 | 388.88 | 0 | 88.88 | 2021-01-01 20:57:44 | 2021-01-01 20:57:44 | 4 | PENDIENTE | N/A | | 0 | |
I need a way to only show records besides the current month and year. example: today is 2021-01-08 I only need to see records besides month 01 and year 2021
This is my sql fiddle playground
I do not understand why it doesn’t output the result I want.
I need it to show me this:
| ID | DOC_ID | DATE | AMOUNT | PAID_AMOUNT | NET_BALANCE | INS_DATE | MOD_DATE | INDEX_ID | STATUS | TRANSPORT | TRANS_DOC_ID | RETURNED | DATES | |----|----------|------------|--------|-------------|-------------|---------------------|---------------------|----------|-----------|-----------|--------------|----------|-------| | 30 | 41514635 | 2020-01-01 | 188.88 | 0 | 88.88 | 2021-01-01 20:57:44 | 2021-01-01 20:57:44 | 1 | PENDIENTE | N/A | | 0 | | | 30 | 41514635 | 2020-03-12 | 288.88 | 0 | 88.88 | 2021-01-01 20:57:44 | 2021-01-01 20:57:44 | 3 | PENDIENTE | N/A | | 0 | | | 30 | 41514635 | 2020-06-30 | 388.88 | 0 | 88.88 | 2021-01-01 20:57:44 | 2021-01-01 20:57:44 | 4 | PENDIENTE | N/A | | 0 | |
Advertisement
Answer
The condition should be:
strftime('%Y%m', date) <> strftime('%Y%m', CURRENT_DATE)
The modifier '%Y%m' returns only the year/month part of the date, like 202101 and this is what you must compare to the same date part of the current date.
If you also want to include only ID = 30 write the statement like this:
SELECT * FROM ClientRecord
WHERE ID = 30 AND strftime('%Y%m', date) <> strftime('%Y%m', CURRENT_DATE)
ORDER BY date DESC, ID
See the demo.
Results:
> ID | DOC_ID | DATE | AMOUNT | PAID_AMOUNT | NET_BALANCE | INS_DATE | MOD_DATE | INDEX_ID | STATUS | TRANSPORT | TRANS_DOC_ID | RETURNED | DATES > -: | -------: | :--------- | -----: | ----------: | ----------: | :------------------ | :------------------ | -------: | :-------- | :-------- | :----------- | -------: | :---- > 30 | 41514635 | 2021-03-30 | 388.88 | 0 | 88.88 | 2021-01-01 20:57:44 | 2021-01-01 20:57:44 | 4 | PENDIENTE | N/A | | 0 | > 30 | 41514635 | 2020-03-12 | 288.88 | 0 | 88.88 | 2021-01-01 20:57:44 | 2021-01-01 20:57:44 | 3 | PENDIENTE | N/A | | 0 | > 30 | 41514635 | 2020-01-01 | 188.88 | 0 | 88.88 | 2021-01-01 20:57:44 | 2021-01-01 20:57:44 | 1 | PENDIENTE | N/A | | 0 |