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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
