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