asked on

How Does Quick Books store date / time?

I want to export information from specific tables in Quick Books to Microsoft Excel using MS Query.

Specifically, I want to pick up invoice info for all invoices issued in the last year. So, for example, if it was today, all invoices AFTER April 5, 2015.

MS Query offers the ability to do select records based on date criteria. But I don;t know the format they are stored in Quick Books in, so I don't know how to construct the query condition. On a prior EE question, someone suggested using #mm/dd/yyyy# (I think that is the MS Access convention. I also tried the MySQL date format "yyyy-mm-dd".

Neither worked.

Can someone provide the way to set the limit so MS Query gets it AND picks the right range from Quick Books?

Thanks

arnold

I do not know, but the straight forward approach would be to select a sample data and see,
Based on https://community.intuit.com/questions/757333-quickbooks-saved-file-time-wrong
The date time could be stored in Epoch format, Unix TIMESTAMP number of seconds since 01/01/1970 GMT
mysql UNIX_TIMESTAMP()

ASKER CERTIFIED SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Bach

Using QODBC, I use this query to get all invoices from the last 30 days:

SELECT * FROM Invoice 
WHERE {fn TIMESTAMPDIFF(SQL_TSI_DAY, Invoice.TxnDate, {fn CURDATE()})} < 30
ORDER BY Invoice.RefNumber

Open in new window

For the last year, change 30 to 365 (or 366).