Am 23.07.2019 um 16:22 schrieb Steve Leonard: > > 1) I imported a new table using dbbrowser for sqlite from a csv file > one of the fields is named myfdat and the first record contains > '12/01/2011' > > 2) the import created a table with the field myfdt as type text and > the value matches the csv file > > 3) I want to select all records where myfdt is between 11/05/2011 and > 12/30/2011.... have had no success > Could someone please post the correct sql statement to do this? > > queries like this give no results: > select * from mytable > where myfdt > '11/06/2011' > and myfdt < '04/13/2018' > > 4)I have tried several combinations of creating a new field > of type numeric, blob, real, and integer and then tried to update > this new field using several date functions, among them: > update mytable > set myfdt = > substr(myfdt, 7) || "-" || substr(myfdt,4,2) || "-" || substr(myfdt, > 1,2) > the sql ran ok, but the data in the new field is in the yyyy-dd-mm format You are basically on a "good" way - just swap day and month field (yyyy-mm-dd) and you're done. This is still a string though ... update mytable set myfdt = substr(myfdt, 7) || "-" || substr(myfdt,1,2) || "-" || substr(myfdt, 4,2) > > depending on which combination I used, I get either no results > or I get wrong answers because a date like 12/04/2011 is > treat like it is April 12 instead of December 4th. If you want to deal with dates like this, you must "order" the parts of that string in descending order!
Andreas > > Thank you. > Steve > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
