Hi, I run an SQLite-driven report on a database automatically on the first of each month. This has worked fine and correctly for almost a...

How It Works

Get an answer in three easy steps. Here's how it works...

Ask Your Question

1. Ask Your Question

Enter your SQLite question at the top of this page and click Get An Answer.

Pick Your Priority

2. Pick Your Priority

Tell us how quickly you want your SQLite question answered.

Get An Answer

3. Get An Answer

Connect with your programmer via online chat or telephone call.

Get An Answer 

Answer

Customer
Hi, I run an SQLite-driven report on a database automatically on the first of each month. This has worked fine and correctly for almost a year, but when the report ran on the 1st of January 2011 I realised there was a problem. The results that the report returned were incorrect on that day. The only thing I can imagine that could have caused these incorrect results, is that the date() function returned an incorrect date. So I wonder if my function is wrong and was hoping somebody could have a look at it. This is what I use: date('now','localtime','start of year','-6 months','-1 day') This particular function is meant to return the 31st of June of the previous year. So if the statement was executed on the 2011-01-01 I expect it to return "2010-06-31". However, this does not seem to have been the case when it ran on the 1st of January. Is my function above incorrect? When I ran the report again on the 4th of January, it returned the correct result. I can't figure out what went wrong. Any help would be greatly appreciated! Thanks
Last updated
srl309
Programmer
I am confused 31'st june doesn't june have 30 days sqlite> select date('now','localtime','start of year', '-6 months', '-1 day'); 2010-06-30 the function seems okay what was the incorrect result. was it off by a day or an incorrect value like -1 ***-***-**** 3697--17
Posted
Customer
You are right, of course. I meant the 30th of June. The result seems to have been off by a year. So what I do once a month is run a report on the Last Financial Year. The report should have produced data for the period from 2009-07-01 to 2010-06-30. Instead, it returned the data for the period 2010-07-01 to 2011-06-30. I will show you the full WHERE clause I use, just to make sure my thinking process is correct. It has always worked fine, except for the on the 1st of January. Just to clarify: the column "SQLiteDate" cointains the date I am checking. Hope it makes sense. ------------------- SELECT * FROM tableName WHERE SQLiteDate < (case when(strftime('%m', 'now')>"07") then date('now','localtime','start of year','+6 months') else date('now','localtime','start of year','-6 months') end) AND SQLiteDate > (case when(strftime('%m', 'now')>"07") then date('now','localtime','start of year','-6 months','-1 day') else date('now','localtime','start of year','-1 year','-6 months','-1 day') end)
Posted
srl309
Programmer
We would espect same results now as of 01/01/11 sinces month is not greater than 07 sqlite> select date('now','localtime','start of year','-6 months'); 2010-07-01 sqlite> select date('now','localtime','start of year','-1 year','-6 months','-1 day'); 2009-06-30 Using the date and time of 01/01/11 select case when(strftime('%m','2011-01-01 02:34:56')>"07") then date('now','localtime','start of year','+6 months') else date('now','localtime','start of year','-6 months') end; returns 2010-07-01 select case when(strftime('%m','2011-01-01 02:34:56')>"07") then date('now','localtime','start of year','-6 months','-1 day') else date('now','localtime','start of year','-1 year','-6 months','-1 day') end; returns 2009-06-30 are you sure the time you ran it was on the 01/01/11 SELECT strftime('%s','now'); = ***-***-**** select strftime('%s', 'now', 'localtime'); = ***-***-**** you dont have local time in the case when statement that is probably the problem. Your function seems fine the problem is probably your local time is faster than the Universal Coordinated Time given by 'now'
Posted
Get An Answer 

quoteTestimonialsquote

About ExpertHelp

ExpertHelp is changing the way you connect with service professionals.

Whether you have a quick question while preparing your taxes, troubleshooting a computer problem, or need to hire an attorney, ExpertHelp is the most convenient and affordable way to connect with the right service professional to get the job done.

ExpertHelp has been in business since 2011, is an A+ Rated Better Business Bureau accredited member, and offers a 100% satisfaction guarantee on every question you ask!

Get An Answer 

More SQLite Questions...

Date
Questions

11/9/2011
How to use SQLite in an ASP.net app?

10/12/2011
How to unstall sqLite on Windows 7?

Ask Your SQLite Question & Get An Answer Now!