Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread Keith Medcalf
update LSOpenProjects set billdate = null where len(rtrim(billdate)) < 10; >Thanks for this, but how do I set a value to null? I thought null and '' >were the same, but now I see it is not. '' means that "the value is an empty string" whereas null means there is no value/the value is

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread jose isaias cabrera
"Igor Tandetnik" wrote... On 9/1/2014 12:02 AM, jic wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t But this is not working correctly, as the beginning of the result, there is no number because billdate is empty. So, I get a list like this:

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread jose isaias cabrera
"Darren Duncan" wrote... On 2014-09-01, 6:50 PM, jose isaias cabrera wrote: Thanks for this, but how do I set a value to null? insert into foo (myfield) values (null); That's one way. If you're using some wrapper API, then the host language's analagy of an undefined value, eg undef in

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread jose isaias cabrera
"Darren Duncan" wrote... A common logical error that may be affecting you is, do your dates include a time portion or are they just year-month-day? If they include a time portion, then records from Dec 31 likely won't be counted as your 'between' arguments may be equivalent to '2013-12-31

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread Darren Duncan
On 2014-09-01, 6:50 PM, jose isaias cabrera wrote: Thanks for this, but how do I set a value to null? insert into foo (myfield) values (null); That's one way. If you're using some wrapper API, then the host language's analagy of an undefined value, eg undef in Perl, should do it. I

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread Darren Duncan
A common logical error that may be affecting you is, do your dates include a time portion or are they just year-month-day? If they include a time portion, then records from Dec 31 likely won't be counted as your 'between' arguments may be equivalent to '2013-12-31 00:00:00'. -- Darren Duncan

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread jose isaias cabrera
"Stephen Chrzanowski" wrote... Untested, but first whack at it; select * from yourtables order by ifnull(billdate,bdate) Criteria met: - A date posted prioritizing billdate and then bdate - Sorted based on date yeah, I had tried that before and no cigar... :-) Thanks, though. josé

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread jose isaias cabrera
"Keith Medcalf" wrote... I have to create some reporting and I need to report on customers amount1 and amount2 two based on the whole year of 2013. There are two dates that are to be used as input: billdate and bdate. I can do one or the other, but, I can not seem to get the correct date

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread jose isaias cabrera
"Darren Duncan" wrote... On 2014-08-31, 9:35 PM, Darren Duncan wrote: On 2014-08-31, 9:10 PM, jose isaias cabrera wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread Petite Abeille
On Sep 1, 2014, at 9:19 PM, Keith Medcalf wrote: > >> On Sep 1, 2014, at 6:00 PM, Igor Tandetnik wrote: >> >>> (case when billdate != '' then billdate else bdate end) >> >> Or, more succinctly: >> >> coalesce( nullif( billdate, '' ), bdate ) >> >>

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread Keith Medcalf
>On Sep 1, 2014, at 6:00 PM, Igor Tandetnik wrote: > >> (case when billdate != '' then billdate else bdate end) > >Or, more succinctly: > >coalesce( nullif( billdate, '' ), bdate ) > >(To OP: empty strings are E V I L. Don't use them. Ever.) Unless of course it is one or

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread Petite Abeille
On Sep 1, 2014, at 6:00 PM, Igor Tandetnik wrote: > (case when billdate != '' then billdate else bdate end) Or, more succinctly: coalesce( nullif( billdate, ‘’ ), bdate ) (To OP: empty strings are E V I L. Don’t use them. Ever.)

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread Igor Tandetnik
On 9/1/2014 12:02 AM, jic wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t But this is not working correctly, as the beginning of the result, there is no number because billdate is empty. So, I get a list like this: Co01|13016.16|20024.46| How is "absence of

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

2014-09-01 Thread Andy Ling
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: 01 September 2014 15:30 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] HELP sqlite3 used in vxworks has some problem > > >

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread John McKown
On Sun, Aug 31, 2014 at 11:02 PM, jic wrote: > > Greetings! > > I have to create some reporting and I need to report on customers amount1 > and amount2 two based on the whole year of 2013. There are two dates that > are to be used as input: billdate and bdate. I can do

Re: [sqlite] SQLite Database Browser v3.3.0 released

2014-09-01 Thread Simon Slavin
On 1 Sep 2014, at 3:32pm, John McKown wrote: > On Mon, Sep 1, 2014 at 2:21 AM, wrote: > >> v3). "Fluffy Pink Rabbits of Doom for SQLite" has a certain >> appeal at this point... ;) > > You might want to do a Google, or other, search on

Re: [sqlite] SQLite Database Browser v3.3.0 released

2014-09-01 Thread John McKown
On Mon, Sep 1, 2014 at 2:21 AM, wrote: > On 2014-08-31 16:07, Richard Hipp wrote: >> >> On Sun, Aug 31, 2014 at 11:49 AM, wrote: > > >>> >>> We've updated everything we control, and just pushed out a v3.3.1 >>> release that uses the new project

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

2014-09-01 Thread Richard Hipp
On Mon, Sep 1, 2014 at 10:15 AM, Andy Ling wrote: > > -Original Message- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Richard Hipp > > Sent: 01 September 2014 14:39 > > To: General Discussion of SQLite

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

2014-09-01 Thread Andy Ling
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: 01 September 2014 14:39 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？ > > Please

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

2014-09-01 Thread Richard Hipp
On Mon, Sep 1, 2014 at 7:04 AM, Andy Ling wrote: > > This can be fixed by changing the patch to something like... > > #if OS_VXWORKS > }else if( errno==0x380003 || errno == 13){ /* == > S_dosFsLib_FILE_NOT_FOUND */ > rc = SQLITE_IOERR_DELETE_NOENT; > #endif > >

Re: [sqlite] How should i found time difference between two events in sqlite3 table , like on off status

2014-09-01 Thread Clemens Ladisch
sanjeev wrote: > sqlite> select sdata ,timestamp from tbl_sensor where nid=4 and timestamp >> =date('now','-4 days') order by timestamp ASC ;0|2014-08-28 > 04:00:320|2014-08-28 04:56:420|2014-08-28 04:57:410|2014-08-28 > 04:58:410|2014-08-28 04:59:410|2014-08-28 05:22:410|2014-08-28 > [...] This

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

2014-09-01 Thread Andy Ling
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Jan Nijtmans > Sent: 01 September 2014 12:29 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？ > >

[sqlite] How should i found time difference between two events in sqlite3 table , like on off status

2014-09-01 Thread sanjeev
sqlite> select sdata ,timestamp from tbl_sensor where nid=4 and timestamp >=date('now','-4 days') order by timestamp ASC ;0|2014-08-28 04:00:320|2014-08-28 04:56:420|2014-08-28 04:57:410|2014-08-28 04:58:410|2014-08-28 04:59:410|2014-08-28 05:22:410|2014-08-28 05:23:410|2014-08-28

[sqlite] Sorting by month with two dates input

2014-09-01 Thread jic
Greetings! I have to create some reporting and I need to report on customers amount1 and amount2 two based on the whole year of 2013. There are two dates that are to be used as input: billdate and bdate. I can do one or the other, but, I can not seem to get the correct date when using

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

2014-09-01 Thread Jan Nijtmans
2014-09-01 13:04 GMT+02:00 Andy Ling : > Although this doesn't feel right having to keep extending the list of error > codes that are checked. > I'm not sure what else can be done though. Maybe some compile options that > let you choose. Here is a list of possible

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

2014-09-01 Thread Andy Ling
> -Original Message- > From: Andy Ling > Sent: 29 August 2014 16:15 > To: '???'; sqlite-users@sqlite.org > Subject: RE: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？ > > I would add a printf statement in unixDelete rather than rely on a break point > working. > I

Re: [sqlite] Sorting by month with two dates input

2014-09-01 Thread Stephen Chrzanowski
Untested, but first whack at it; select * from yourtables order by ifnull(billdate,bdate) Criteria met: - A date posted prioritizing billdate and then bdate - Sorted based on date On Mon, Sep 1, 2014 at 1:55 AM, Keith Medcalf wrote: > > >>I have to create some reporting

Re: [sqlite] SQLite Database Browser v3.3.0 released

2014-09-01 Thread justin
On 2014-08-31 16:07, Richard Hipp wrote: On Sun, Aug 31, 2014 at 11:49 AM, wrote: We've updated everything we control, and just pushed out a v3.3.1 release that uses the new project naming (with no functional changes). ... and it turns out there's already a project