I think the logic might have been to subtract the number of days in the previous month from the current date. E.g. 2007/03/25 -28 (days in Feb) will give 2007/02/25 (spot on) 2007/10/31 -30 (days in Sept) will give 2007/10/01 (as good as you can get) 2007/09/30 -31 (days in Aug) will give 2007/08/30 (spot on)
So looks like it works well in all cases except for the last 2 days in March For adding a month the logic would be to add the number of days in the month (of the date you are adding to). E.g. if date was 2007/09/30 then add 30 days -> 2007/10/30 <Note: I think that this approach for adding a month always works> Two solutions I can see to your problem A) special code to handle Feb B) always subtract 30 days if subtracting a month. However that means that you will have to always add 30 days if adding a month (which is not perfect) or have a situation where ( 'date' - 1 month) + 1 month does not give you 'date' as its result. Hope this helps :) PS Unfortunately dates are messy, but only way to fix them is to change the spin of the Earth! -----Original Message----- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Friday, 4 January 2008 12:17 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date Problems Look at the Sqlite date functions. File date.c describes them. You may find that a custom function gives you exactly what you want. [EMAIL PROTECTED] wrote: > Hi Kees > > Many thanks for your quick reply, but it doesn't give me the date i > want. > > 2006/03/31 - 1 month should be 2006/02/28 > > whereas > select date('2006-03-31', 'start of month','-1 month') obviously gives > me 2006/02/01 > > > > I need to be able to subtract 1 month from not just a single expression, > but from a 100s of dates that are stored in a table. > > > Richard - I also just spotted your email. Thanks, I agree, it is fuzzy. > However : > > 2006/03/31 minus 1 month : I could accept 28 Feb or 1 Mar as a > reasonable answer and I can make that point to my users. > > 03 March is not reasonable, I can't think of any logic that would give > me that answer and I'm not able to make any case. > > > Thanks > Craig > > > -----Original Message----- > From: Kees Nuyt [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 03, 2008 9:25 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Date Problems > > > Hi Craig, > > On Thu, 3 Jan 2008 08:49:42 +0900, <[EMAIL PROTECTED]> wrote: > >> Hi >> >> Can somebody give any explain to this please. >> >> sqlite> select date("2006-03-31"); >> 2006-03-31 >> --> correct >> >> sqlite> select date("2006-03-31", "-1 month"); >> 2006-03-03 >> --> not correct >> >> >> Can anyone confirm? Any suggestions / workarounds greatfully received! > > Confirmed. > > Better: > select date('2006-03-31', 'start of month','-1 month'); > >> Many thanks > > HTH > >> Craig > -- > ( Kees Nuyt > ) > c[_] > > ------------------------------------------------------------------------ > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > ------------------------------------------------------------------------ > ----- > > Visit our website at http://www.ubs.com > > This message contains confidential information and is intended only > for the individual named. If you are not the named addressee you > should not disseminate, distribute or copy this e-mail. Please > notify the sender immediately by e-mail if you have received this > e-mail by mistake and delete this e-mail from your system. > > E-mails are not encrypted and cannot be guaranteed to be secure or > error-free as information could be intercepted, corrupted, lost, > destroyed, arrive late or incomplete, or contain viruses. The sender > therefore does not accept liability for any errors or omissions in the > contents of this message which arise as a result of e-mail transmission. > If verification is required please request a hard-copy version. This > message is provided for informational purposes and should not be > construed as a solicitation or offer to buy or sell any securities > or related financial instruments. > > > ------------------------------------------------------------------------ ----- > To unsubscribe, send email to [EMAIL PROTECTED] > ------------------------------------------------------------------------ ----- > ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ************************************************************************ The information in this email together with any attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any form of review, disclosure, modification, distribution and/or publication of this email message is prohibited, unless as a necessary part of Departmental business. If you have received this message in error, you are asked to inform the sender as quickly as possible and delete this message and any copies of this message from your computer and/or your computer system network. ************************************************************************ ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
