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]
-----------------------------------------------------------------------------