Well I am interested in fetching 3 records because i need to show them on one screen as the program starts, and reason for showing 3 recording is to maintain next , now and previous button.
Whenever i click next button so it should increment by +1 everytime and show next 3 records, i.e (now) will become previous, next and now will be increment. when i click prev so it should decrement in the same order as incremented.
I am successfully able to run my query and able to fetch 3 records but scenerio changes when it was beginning of the month or end of the month.
SELECT rowid,Month, Day, Advice from MainCategory where Month= 'May ' and Day in ((cast(strftime('%d',date('now','-1 day')) as Integer)),(cast(strftime('%d',date('now')) as Integer)),(cast(strftime('%d',date('now','+1 day')) as Integer)));Above is showing 3 records but when I decremented it by -2 in the first placeholder as
Day in ((cast(strftime('%d',date('now','-2 day')) as Integer)),(cast(strftime('%d',date('now')) as Integer)),(cast(strftime('%d',date('now','+1 day')) as Integer)));So it shows May 2 (which current date), May 3, which is (Tomorrow's date), but May 30 (which is incorrect) it should need to be April 30.
My 2 records are correct but how to solve May 30 to April 30 and this whole scenerio is dynamic. I am also enclosing scheme of my database for ready reference, but please run it in sqlite database which is a Mozilla add-on.
CREATE TABLE "MainCategory" ("Month" TEXT,"Day" TEXT,"Advice" BLOB) and contains 365 days record.If you want the database so i can mail it to you