Sqlite filter as date return error with strftime function

Asked

Viewed 342 times

1

Hello I have the error in the following code

SELECT L.*, C.DESCRICAO AS CATEGORIA , CASE WHEN L.TIPO_LANCAMENTOS = 'C' THEN 0 ELSE 1 END AS ICONE
FROM LANCAMENTOS L JOIN CATEGORIAS C ON(C.CODIGO = L.COD_CATEGORIA)
WHERE strftime('%m', L.DATA) = '10'
AND strftime('%Y', L.DATA) = '2016'

The select works perfectly already I surrounded this query and is correct the problem starts exactly in Where which is of paramount importance this condition for my filter. I believe that with this code was to be bringing all the records of the month 10 and year 2016, but nothing happens. I tried to make this change.

SELECT L.*, C.DESCRICAO AS CATEGORIA , CASE WHEN L.TIPO_LANCAMENTOS = 'C' THEN 0 ELSE 1 END AS ICONE
FROM LANCAMENTOS L JOIN CATEGORIAS C ON(C.CODIGO = L.COD_CATEGORIA)
WHERE strftime('%m', L.DATA = '10')
AND strftime('%Y', L.DATA = '2016')

I changed the position of closing the parentheses, but this way it does not run the filter and ends up bringing all the records of the table every month and every year and that is not what I want. Thanks for your help.

delphi sqlite mobile date

  • If you mount SQL like this: SELECT strftime('%m', L.DATA) AS MES, strftime('%Y', L.DATA) AS ANO FROM LANCAMENTOS L JOIN CATEGORIAS C ON(C.CODIGO = L.COD_CATEGORIA) What will be the return?

  • That way I didn’t try, but I need to bring all the fields in this table, the date would be just to run the filter

  • Yes, I understood that. But the return of SQL will help us see how to fix the filter. You can let us know the result of this SQL that I suggested to you?

  • I did what you said it returns NULL in the fields MES and YEAR I believe you are not able to find the values

  • There’s the problem... the field L.DATA is the type Date? Maybe you need to convert the field to Date. Do something like this: strftime('%m', datetime(L.DATA, 'unixepoch'))

  • It’s DATETIME the field so I’m finding it strange wasn’t supposed to happen this mistake.

  • Let’s go continue this discussion in chat.

Show 2 more comments

1 answer

1

Do so: 

SELECT L.*, C.DESCRICAO AS CATEGORIA , CASE WHEN L.TIPO_LANCAMENTOS = 'C' THEN 0 ELSE 1 END AS ICONE
FROM LANCAMENTOS L JOIN CATEGORIAS C ON(C.CODIGO = L.COD_CATEGORIA)
WHERE strftime('%m-%Y', L.DATA ) = '10-2016'

Browser other questions tagged delphi sqlite mobile date

You are not signed in. Login or sign up in order to post.