Re: [sqlite] Sqlite DATE() function. Add a variable amount of months.
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> 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
Re: [sqlite] Sqlite DATE() function. Add a variable amount of months.
> 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, datepbwrote: > > 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
[sqlite] Sqlite DATE() function. Add a variable amount of months.
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