Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?
Dear all. Sorry! Ashes on my head :-) Last night it was 3.00 in the morning before I got off my screen ... Adding single quotes AND actually using sqlite did it (see below). Txs again for all your help and patience. bernie sqlite> select strftime('%w','2010-03-21'); 0 sqlite> select strftime('%w','2010-03-22'); 1 sqlite> select strftime('%w','2010-03-23'); 2 sqlite> select strftime('%w','2010-03-24'); 3 sqlite> select strftime('%w','2010-03-25'); 4 sqlite> select strftime('%w','2010-03-26'); 5 sqlite> select strftime('%w','2010-03-27'); 6 sqlite> select strftime('%w','2010-03-28'); 0 ----- Original Message From: Nicolas Williams To: Bernie Reiter Cc: sqlite-users@sqlite.org Sent: Tue, 23 March, 2010 20:29:28 Subject: Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ? On Tue, Mar 23, 2010 at 07:25:56PM +, Bernie Reiter wrote: > I am checking this for Sunday, March 21st 2010, Monday, 22nd March 2010 and > Tuesday, 23nd March 2010: > > Sunday, March 21st 2010:SELECT strftime('%w',2010-03-21); => 6 You need single quotes around the date value. 2010-03-21 == 2010 + -03 + -21 -- surely not what you intended. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?
On Tue, Mar 23, 2010 at 2:58 PM, Bernie Reiter wrote: > Nico, thanks for your quick reply. > > I have copied the function strftime('%Y-%m-%d', ...) directly the sqlite > documentation > and substituted the ... with my date string. > Unfortunately, your advice doesn't seem to convince my Linux box, > neither with a straight single quote nor with a "backwards leaning" single > quote > > [liveu...@localhost ~]$ SELECT strftime('%w','2010-03-21'); > bash: syntax error near unexpected token `(' > > > [liveu...@localhost ~]$ SELECT strftime('%w',`2010-03-21`); > bash: syntax error near unexpected token `(' > Bernie, you need to take a break and get a cup of coffee ;-) Then, make sure you start sqlite before issuing a sqlite command. As shown above, you are trying to query a db from bash, bash has no clue what to do. > > > > > - Original Message > From: Nicolas Williams > To: Bernie Reiter > Cc: sqlite-users@sqlite.org > Sent: Tue, 23 March, 2010 20:29:28 > Subject: Re: [sqlite] Computing day of week the using strftime() function - > Feature or bug ? > > On Tue, Mar 23, 2010 at 07:25:56PM +, Bernie Reiter wrote: >> I am checking this for Sunday, March 21st 2010, Monday, 22nd March 2010 and >> Tuesday, 23nd March 2010: >> >> Sunday, March 21st 2010: SELECT strftime('%w',2010-03-21); => 6 > > You need single quotes around the date value. > > 2010-03-21 == 2010 + -03 + -21 -- surely not what you intended. > > Nico > -- > > > > > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?
On 3/23/10 8:58 PM, Bernie Reiter wrote: > Nico, thanks for your quick reply. > > I have copied the function strftime('%Y-%m-%d', ...) directly the sqlite > documentation > and substituted the ... with my date string. > Unfortunately, your advice doesn't seem to convince my Linux box, > neither with a straight single quote nor with a "backwards leaning" single > quote > > [liveu...@localhost ~]$ SELECT strftime('%w','2010-03-21'); > bash: syntax error near unexpected token `(' > > > [liveu...@localhost ~]$ SELECT strftime('%w',`2010-03-21`); > bash: syntax error near unexpected token `(' > You're talking to bash, not sqlite. Igmar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?
Nico, thanks for your quick reply. I have copied the function strftime('%Y-%m-%d', ...) directly the sqlite documentation and substituted the ... with my date string. Unfortunately, your advice doesn't seem to convince my Linux box, neither with a straight single quote nor with a "backwards leaning" single quote [liveu...@localhost ~]$ SELECT strftime('%w','2010-03-21'); bash: syntax error near unexpected token `(' [liveu...@localhost ~]$ SELECT strftime('%w',`2010-03-21`); bash: syntax error near unexpected token `(' Txs bernue - Original Message From: Nicolas Williams To: Bernie Reiter Cc: sqlite-users@sqlite.org Sent: Tue, 23 March, 2010 20:29:28 Subject: Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ? On Tue, Mar 23, 2010 at 07:25:56PM +, Bernie Reiter wrote: > I am checking this for Sunday, March 21st 2010, Monday, 22nd March 2010 and > Tuesday, 23nd March 2010: > > Sunday, March 21st 2010:SELECT strftime('%w',2010-03-21); => 6 You need single quotes around the date value. 2010-03-21 == 2010 + -03 + -21 -- surely not what you intended. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?
On Tue, Mar 23, 2010 at 07:25:56PM +, Bernie Reiter scratched on the wall: > Sunday, March 21st 2010:SELECT strftime('%w',2010-03-21); => 6 > Monday, March 22nd 2010: SELECT strftime('%w',2010-03-22); => 5 > Tuesday, March 23nd 2010: SELECT strftime('%w',2010-03-23); => 4 Try putting quotes around those... e.g.: strftime('%w','2010-03-21'); The way you've written it, you're computing 2010 minus 03 minus 21, e.g. the integer 1986. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?
On Tue, Mar 23, 2010 at 12:25 PM, Bernie Reiter wrote: > Dear Dr. Hipp, > SELECT strftime('%w',2010-03-22 22:12:40) ; => SQL error near "22": syntax > error Add quotes: SELECT strftime('%w','2010-03-22 22:12:40'); See the section Examples at http://www.sqlite.org/lang_datefunc.html Stephan > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Stephan Wehner -> http://stephan.sugarmotor.org (blog and homepage) -> http://loggingit.com -> http://www.thrackle.org -> http://www.buckmaster.ca -> http://www.trafficlife.com -> http://stephansmap.org -- http://blog.stephansmap.org -> http://twitter.com/stephanwehner / @stephanwehner ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?
On Tue, Mar 23, 2010 at 07:25:56PM +, Bernie Reiter wrote: > I am checking this for Sunday, March 21st 2010, Monday, 22nd March 2010 and > Tuesday, 23nd March 2010: > > Sunday, March 21st 2010:SELECT strftime('%w',2010-03-21); => 6 You need single quotes around the date value. 2010-03-21 == 2010 + -03 + -21 -- surely not what you intended. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Computing day of week the using strftime() function - Feature or bug ?
Dear Dr. Hipp, SQLite is a wonderful tool. Thanks a LOT ! I am working every day with it and love it more and more :-) Only sometimes I am confused. Maybe the community can help or enlighten me. Thanks again bernie 1) My environment: -- [liveu...@localhost ~]$ date Mon Mar 22 22:12:40 CET 2010 Platform: Fedora V12 64 bit live version on AMD 64bit single core 1800MHz [liveu...@localhost ~]$ sqlite3 --version 3.6.20 ("yum install sqlite" does not update to current version: 2010-Mar-09 - Version 3.6.23) Package sqlite-3.6.20-1.fc12.x86_64 already installed and latest version Nothing to do 2) Excerpts from the sqlite online documentation (http://sqlite.org/lang_datefunc.html) --- Date And Time Functions: Time Strings A time string can be in any of the following formats: 1. -MM-DD 2. -MM-DD HH:MM 3. -MM-DD HH:MM:SS ... The strftime() function also takes a format string as its first argument. FunctionEquivalent strftime() date(...) strftime('%Y-%m-%d', ...) %w day of week 0-6 with sunday==0 3) Now me: -- I am checking this for Sunday, March 21st 2010, Monday, 22nd March 2010 and Tuesday, 23nd March 2010: Sunday, March 21st 2010:SELECT strftime('%w',2010-03-21); => 6 Monday, March 22nd 2010: SELECT strftime('%w',2010-03-22); => 5 Tuesday, March 23nd 2010: SELECT strftime('%w',2010-03-23); => 4 Monday, 22nd March 2010 22:12:40 h: SELECT strftime('%w',2010-03-22 22:12:40) ; => SQL error near "22": syntax error Here is the verbatim copy from my command line: [liveu...@localhost ~]$ sqlite3 SQLite version 3.6.20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT strftime('%w',2010-03-21); 6 sqlite> SELECT strftime('%w',2010-03-22); 5 sqlite> SELECT strftime('%w',2010-03-23); 4 sqlite> SELECT strftime('%w',2010-03-24); 3 sqlite> SELECT strftime('%w',2010-03-25); 2 sqlite> SELECT strftime('%w',2010-03-26); 1 sqlite> SELECT strftime('%w',2010-03-27); 0 sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users