It works perfectly, Thanks a lot! Sorry for the newbie question !
Pavel Ivanov-2 wrote: > >> 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 > > -- View this message in context: http://old.nabble.com/Sqlite-DATE%28%29-function.-Add-a-variable-amount-of-months.-tp31802785p31803136.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