Skip to content
Advertisement

strftime() with not giving the correct results

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 | 

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement