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
