Select error

[aragjos]

Details

Sorry for my english, i have a table with some records and a date field when i try to retrieve data using date field (fecha_obs).

Steps to reproduce

SELECT cod_proyev, nro_obsproy, fecha_obs
FROM bda_regobserv where cod_proyev = 'PPOD' and fecha_obs >=01/12/2020; this caser ok

SELECT cod_proyev, nro_obsproy, fecha_obs
FROM bda_regobserv where cod_proyev = 'PPOD' and fecha_obs =31/12/2020; not work

SELECT cod_proyev, nro_obsproy, fecha_obs
FROM bda_regobserv where cod_proyev = 'PPOD' and fecha_obs <=04/01/2021; not work

with python the same error don´t retrieve anything

Operating system

windows 10 64 bits

SQLiteStudio version

v3.2.1

[pawelsalawa]

What do you mean by "not work"? Do you get an error? Or you get wrong results?

[aragjos]

the querys don´t retrieve data, but exist

[pawelsalawa]

Put your date between quotes, like:

SELECT cod_proyev, nro_obsproy, fecha_obs
FROM bda_regobserv where cod_proyev = 'PPOD' and fecha_obs >= "01/12/2020";

SELECT cod_proyev, nro_obsproy, fecha_obs
FROM bda_regobserv where cod_proyev = 'PPOD' and fecha_obs = "31/12/2020";

SELECT cod_proyev, nro_obsproy, fecha_obs
FROM bda_regobserv where cod_proyev = 'PPOD' and fecha_obs <= "04/01/2021";

[aragjos]

Yes I did but nothing i´m using " or simple ' but nothing

[aragjos]

When usin SELECT cod_proyev, nro_obsproy, fecha_obs
FROM bda_regobserv where cod_proyev = 'PPOD' and fecha_obs <= "04/01/2021"; retrieve only de data of month 01 year 2021 but de data of the last year don´t show

[pawelsalawa]

And you're saying the same happens if you try it with Python?

[thesagarhiray]

def test(a):
print(a-1)
a=a+1
if a<=7:
test(a+1)
print(a-1)
return
a=3
test(a)

#after return why if block run

[pawelsalawa]

What???

[aragjos]

yes

[aragjos]

   sql_select = 'SELECT cod_proyev, nro_obsproy, fecha_obs, nbr_objeto, observaciones' \
                 ' FROM bda_regobserv where cod_proyev = ? and fecha_obs >=? and fecha_obs <=?'

    self.cursor.execute(sql_select, (cod_proyecto, dE_fchdesde, dE_fchhasta,))
    records = self.cursor.fetchall()

[pawelsalawa]

I see. The problem lays in comparision method of fecha_obs and your argument. SQLite3 does not have real date or datetime types. It treats them as simple text.
To compare dates, I'd recommend to use dates in format, that gives same results, no matter if you compare them as "Date" or "Text". Such format would be for example YYYY-MM-DD.

In addition, if you ever wanted to do some math on dates (subtracting/adding days), you would need to use date functions: https://sqlite.org/lang_datefunc.html
The thing is, that these functions accept time only in certain formats: https://sqlite.org/lang_datefunc.html#time_values so again you would need to switch to different format - like the one above.

[aragjos]

O let me see

[pawelsalawa]

Please note, that that also applies to any values stored in your fecha_obs column/table.