> 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!
I've seen other replies that show why SQLite does this sort of thing, so
I shan't go there.
In order to determine a "work-around", you must first decide what you
want to happen for any given date. Since there are only two months in
the year that have exactly the same number of days as the previous month
(January and August), you'll likely need to make some adjustment,
depending on your desired results, for any date that falls on the last
day of the month.
For instance, if you simply want the last day of the previous month,
then "date minus daynumber(date)" will do that every time, regardless of
what the preceding month is, or whether or not it's a leap year.
What it sounds like you want is to get the same date in the previous
month where possible, and to adjust it in other situations. Probably
not worth creating a single SQL statement to do that, but a program that
reads all your data and does the right thing would be relatively simple
to create. If you must do it as SQL, then I'd suggest a three step
process (provided I haven't missed anything. :) The steps below assume
that you are saving the new date to a new column, otherwise you run into
"how do I know which records I've already changed" issues.
1. Select all the dates that are the 31st of any month, and set the
result to the end of the previous month, e.g. select date(dateColumn,
"-31 days")
2. Select all March dates that are greater than the 28th and set the
result to the end of February.
3. Set the result for the rest of the rows by subtracting one month
from the date.
Hope that helps.
Brad
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
