unable to use date fields in sqlite

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

unable to use date fields in sqlite

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

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.

Thank you.
Steve

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unable to use date fields in sqlite

Andreas Kretzer
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unable to use date fields in sqlite

Andreas Kretzer
In reply to this post by Steve Leonard
Hi Steve,

beside what I said in my last statement, you should also
have a look at

    https://www.sqlite.org/datatype3.html
    https://www.sqlite.org/lang_datefunc.html

This may help you to understand, how SQLite handles specific
data types, that might be understood (or at least be handled)
different than in other DB engines.

Andreas

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
>
> 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.
>
> Thank you.
> Steve
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unable to use date fields in sqlite

nomad
In reply to this post by Andreas Kretzer
On Tue Jul 23, 2019 at 04:41:59PM +0200, Andreas Kretzer wrote:
> Am 23.07.2019 um 16:22 schrieb Steve Leonard:
> >
> > 4)I have tried several combinations of creating a new field
> > of type numeric, blob, real, and integer and then tried to update

If you want to work with date *strings* of the form 'yyyy-mm-dd' you
probably want to use TEXT.

> > 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'

If you have modified your data to be yyyy-mm-dd then your queries
should also be in that format:

    SELECT * FROM mytable
    WHERE myfdt > '2011-11-06'
    AND myfdt < '2018-04-13'

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unable to use date fields in sqlite

D Burgess
In reply to this post by Andreas Kretzer
Note https://www.sqlite.org/datatype3.html says:
"Applications can chose to store dates and times in any of these
formats and freely convert between formats using the built-in date and
time functions."
This is partially true. The date and time functions will optionally
parse 'a timezone indicator of the form "[+-]HH:MM" or just "Z"'.
The same functions will not create this optional form. i.e. strftime
is missing '%z' as a conversion specifier.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users