> 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