Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?

[Bernie Reiter]

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 ?

[P Kishor]

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 ?

[Igmar Palsenberg]

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 ?

[Bernie Reiter]

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 ?

[Jay A. Kreibich]

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 ?

[Stephan Wehner]

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 ?

[Nicolas Williams]

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 ?

[Bernie Reiter]

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