Writing, adding and using your own functions within SQLite is pretty easy. That's probably your best bet to solve this problem.
-T > -----Original Message----- > From: Fowler, Jeff [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 17, 2008 11:59 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Date arithmetic question > > Guys, > > I guess I'm the newest SQLite person on this email list and I > know I'm definitely the dumbest. It seems like a lot of you > are trying to justify why two dates that are one minute apart > can have a function say they're one month apart. Don't look > at it that way. Back when mainframes and dinosaurs ruled the > world I used to be a fairly decent programmer, now I run a > small company, so today I'm coming from a business standpoint > not a programmer one. > > To give some background, we're in the process of embedding > SQLite into our application, and yes it's an awesome product. > Our software sits on top of huge data warehouses (hundreds of > millions of rows) which are typically either Oracle or SQL > Server. We run queries against these databases and store the > result sets in SQLite, where we run subsequent queries to > filter & format the output. > > A huge number of businesses are date driven. Publishers want > to renew subscriptions before they expire. Insurance > companies need to renew policies. Our largest client sells > service contracts which have a start & end date. So it's an > everyday occurrence for a manager to want to know how many > customers will expire within the next three months, or what > an average contract length is in months. > > My request was for a new date function that returns the > difference, or "calendar interval" if you prefer, between two > dates. Without such a function we must say: > WHERE (strftime('%Y', LaborEndDate)*12+strftime('%m', > LaborEndDate)) - (strftime('%Y', > LaborStartDate)*12+strftime('%m', LaborStartDate)) < 3 > > Wow. This is quite a mouthfull for something that's so > commonly needed, and it's harder to generate SQL > automatically when a user clicks a checkbox. Clearly it's far > simpler (and easier to program) if we could say: > WHERE datediff('month', LaborEndDate, LaborStartDate) < 3 > > Datediff also supports years, weeks, days, hours, seconds, > and milliseconds. It's just a quick & easy way to make life > easier. But.. of course I know the SQLite team can't slap in > every enhancement that somebody suggests; so I just wanted to > explain why this would be useful for some of us. We do have a > vested interest in the product! > > - Jeff > > > ________________________________ > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thu 1/17/2008 9:21 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Date arithmetic question > > > > Gerry Snyder <[EMAIL PROTECTED]> wrote: > > [EMAIL PROTECTED] wrote: > > > > > > So datediff('month', '2008-02-01 23:59:59','2008-01-31 > 00:00:00') should > > > return 1 even though the difference is really only 1 > second? Seems > > > goofy to me.... > > > > > > > > > > I have been staring at this until I'm getting goofy. > > > > Written as it is, isn't the time interval 1 second short of > two days? > > > > If you want an interval of 1 second shouldn't it be > > > > datediff('month', '2008-02-01 00:00:00','2008-01-31 23:59:59') > > > > ? > > > > > > Gerry, more confused than usual (as usual) > > > > Yeah. I got it backwards. Sorry. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -------------------------------------------------------------- > --------------- > To unsubscribe, send email to [EMAIL PROTECTED] > -------------------------------------------------------------- > --------------- > > > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
