> The main issue for me here is that the +1 is inside the string '+1 month'
> and i thus cant use a subquerry of the type SELECT date('now','+(SELECT id
> FROM table) month');
You can do like this:
SELECT date('now','+' || (SELECT id FROM table) || ' month');
Pavel
On Wed, Jun 8, 2011 at 1:42 PM, datepb <kevin.guille...@etu.enseeiht.fr> wrote:
>
> Hello,
>
> This may seem a very simple issue it is giving me some very hard time.
> I need to add a variable amount of months to a date.
>
> According to the doc, this is done for adding 1 month :
> SELECT date('now','+1 month');
>
>
> And I would need something like SELECT date('now','+$i month');
>
>
>
>
> This SELECT is inside a trigger that is created say at the beginning of the
> day and then the value of $i changes during the day depending on feed in the
> different tables, so i need to do it in SQL (I cant call the piece of code
> conatining the trigger later during the day; so for exemple a bash(perl,
> whatever...) script calling the SQL code with update value of $i is not good
> either)
>
>
>
> Now for trying to do it in sql:
>
> The main issue for me here is that the +1 is inside the string '+1 month'
> and i thus cant use a subquerry of the type SELECT date('now','+(SELECT id
> FROM table) month');
>
> I cant either build a string and then do SELECT date('now',SELECT mystring
> FROM table2);
>
>
>
> The two other functions for dates in sqlite are:
>
> -strftime(format, timestring, modifier, modifier, ...) which is equivalent
> to date() in this way: date(...) = strftime('%Y-%m-%d', ...)
> It works perfectly for getting results on dates like:
> SELECT strftime('%m','now') - strftime('%m','2004-01-01 02:34:56');
> would give the difference in month between the dates, but unfortunately the
> modifier to change the date in the same as in date:
> SELECT strftime('%m','now','+3 month');
> So no help from here.
>
> -julianday(timestring, modifier, modifier, ...) the number of days since
> noon in Greenwich on November 24, 4714 B.C.
> This is "great" (let's say doable) for adding a variable number of days like
> this:
> SELECT DATE( SELECT julianday('now')+id FROM table );
>
> But useless for adding months (would have to take into account 30 or 31 days
> month etc.. years).
>
>
>
> I really looked on forums, google, etc and didnt find any solution.
>
> Doc on the date function in sqlite: http://www.sqlite.org/lang_datefunc.html
>
> Have a nice day, thanks
>
> Harder question: why on earth would someone ever code a function this way
> instead of DATE('now',int, string) ??
>
>
>
>
>
>
>
> --
> View this message in context:
> http://old.nabble.com/Sqlite-DATE%28%29-function.-Add-a-variable-amount-of-months.-tp31802785p31802785.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
