Sep 27 |
Posted by Rajendra Dewani (QODBC Support) on 27 September 2013 08:15 AM
|
Hot Topics [QODBC-Desktop] Troubleshooting - CDKEY has an invalid checksum.
Solved: [QODBC-ALL] Troubleshooting - Windows cannot find fqqbsav.exe | |
Comments (2)
Having problems with QODBC syntax
16 December 2014 11:22 PM
I'm supposed to be pretty decent with Sql, have spent years on MS SQL, so I was surprised that I couldn't write a simple filtered select.
SELECT * FROM TimeTracking WHERE TxnDate > '2014-05-17' gets Invalid operand for operator.
I tried "2014-05-17" and d'2014-05-17' and '#2014-05-17#' which I think works in Access. I hate to go back to kindergarten, but is there a syntax manual for QODBC?
SELECT * FROM TimeTracking WHERE TxnDate > '2014-05-17' gets Invalid operand for operator.
I tried "2014-05-17" and d'2014-05-17' and '#2014-05-17#' which I think works in Access. I hate to go back to kindergarten, but is there a syntax manual for QODBC?
Jack
17 December 2014 10:02 AM
Hi,
The time data in QODBC has its own formation. The date type format is {d 'YYYY-MM-DD'} and the time stamps format is {ts 'YYYY-MM-DD HH:MM:SS.SSS'} The time created field is time stamp type, so the correct query should be like below:
Examples
Date Format
SELECT * from TimeTracking WHERE Txndate >= {d '2005-09-23'}
Time Stamps Format
SELECT * FROM Customer WHERE TimeCreated = {ts '1999-07-29 14:24:18.000'}
SELECT * from InvoiceLine WHERE TimeModified >= {ts '2005-09-23 00:00:00.000'}
Please refer:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2407/61/troubleshooting-problems-with-date-format
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2203/50/how-are-dates-formatted-in-sql-queries-when-using-the-quickbooks-generated-time-stamps
The time data in QODBC has its own formation. The date type format is {d 'YYYY-MM-DD'} and the time stamps format is {ts 'YYYY-MM-DD HH:MM:SS.SSS'} The time created field is time stamp type, so the correct query should be like below:
Examples
Date Format
SELECT * from TimeTracking WHERE Txndate >= {d '2005-09-23'}
Time Stamps Format
SELECT * FROM Customer WHERE TimeCreated = {ts '1999-07-29 14:24:18.000'}
SELECT * from InvoiceLine WHERE TimeModified >= {ts '2005-09-23 00:00:00.000'}
Please refer:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2407/61/troubleshooting-problems-with-date-format
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2203/50/how-are-dates-formatted-in-sql-queries-when-using-the-quickbooks-generated-time-stamps