|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Query using YEAR with dates in MS-Access |
| Author |
Message |
|
|
| Posted : 2009-02-11 11:05:31 |
Hi guys,
I need to compute a Year to Date total paid using the Invoice table. I'm working in MS-Access and need to run the following statement:
SELECT SUM(Subtotal) FROM Invoice WHERE CustomerRefListID = 'some customer ID' AND IsPaid AND TimeCreated BETWEEN date1 AND date2
So far, I have not been able to make that statement work. The date1 and date2 are not properly recognized as dates.
1) I tried with the usual MS-Access syntax: #2009/01/01 00:00:00#. That gives me "a really bad SQL error". It does not even want to execute the statement.
2) I tried with the usual SQL syntax '2009/01/01 00:00:00'. In this case, the statement starts going, but once the date has to be compared, it breaks with a "data mismatch " error
So I searched for another solution and I thought, hey! why not use the YEAR() function. I changed my statement like this:
SELECT SUM(Subtotal) FROM Invoice WHERE CustomerRefListID = 'some customer ID' AND IsPaid AND YEAR(TimeCreated) = 2009
The statement is accepted, but the WHERE clause fails to find any invoice (I have two invoices, one paid and one unpaid for test purposes.)
So I tested some more, and really wondered what is YEAR(TimeCreated) returning?! In the doc it says the year as an Integer... so? I tried this:
SELECT YEAR(TimeCreated) FROM Invoice WHERE CustomerRefListID = 'some customer ID' AND IsPaid
and looked at the result. Well... YEAR(TimeCreated) does return 2009. So why would the WHERE clause not work???
Testing some more, I found out that the YEAR(TimeCreated) within the WHERE clause seems to return the date in Unix format, seemingly in UTC.
Do you agree that this is a bug in QODBC?
And do you have a work around that I could use to get my query to work?
Thank you.
Alexis Wilke |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-11 11:59:14 |
|
|
|
|
|
| Posted : 2009-02-11 13:04:54 |
Hi Tom,
Okay! I got it!
The link you pasted does not tell the solution, but it did show that I still had to use the dates between #...#.
The problem I had was that I used a time of 00:00:00 and that fails. Not too sure who's the "idiot" who fails on such a time. As far as I know it is a legal time in the entire world...
Anyway, by changing my first date to use 00:00:01 it worked.
This does NOT change the fact that there is a bug with the YEAR() function.
When I write the following it fails:
SELECT * FORM Invoice WHERE YEAR(TimeCreated) = YEAR(NOW())
And maybe that's ODBC and not you, but that's still quite incredible that such a simple statement does not work.
Thank you.
Alexis |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-11 20:13:45 |
As per: What Time and Date Functions can be used with QODBC? our syntax for YEAR is:
SELECT {fn YEAR({fn CURDATE()})} AS "CurYear" FROM Company
and this also works:
SELECT {fn YEAR( '2009-09-23 00:00:00.000' )} AS "CurYear" FROM Company
The issue isn't QODBC here. QODBC has functions that work with the correct data types, however, when using MS Access, you are required to use their format and MS Access translates the query to a SQL statement that is sent to QODBC. This isn't always right.
If the query is changed to a pass-through query, then the SQL statement is sent unchanged and standard QODBC syntax can be used. |
|
|
|
|
|
| Posted : 2009-02-12 03:59:19 |
Okay, I'll try the pass-through because this is really annoying.
Now, notice that all your examples show the functions in the operation/result part of the select, not the WHERE clause. My problem was only in the WHERE clause. The simple YEAR() function from MS-Access works just fine in the operation part.
With MS-Access, I cannot use the Jet syntax (with the curly brackets) because it thinks I'm writing a GUID and of course the syntax in that case is wrong for a GUID...
Anyway... the pass-through should keep the code as is and send that to you which means I should not hit these problems anymore!
It could be a good idea to document that clearly somewhere, be it with a forum post and point people there whenever they say they're using MS-Access and that way you just have to give them a link: look here how to use QODBC from MS-Access...
Thank you for your help! |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-12 10:18:43 |
Actually there is a bug in QODBC when using the WHERE clause. This query executes:
Select * from Invoice where {fn YEAR(TxnDate)} = 2009
but doesn't return any records. Why because our best guess on the typing is wrong. The equal fails because the YEAR is a string, but the compared value is a integer. You also can't fix it by doing:
Select * from Invoice where {fn YEAR(TxnDate)} = '2009'
as the function thinks it must be a numeric. I've made it work correctlly by doing:
Select * from Invoice where {fn CONVERT( {fn YEAR(TimeCreated)}, SQL_VARCHAR)} = '2009'
Select * from Invoice where {fn CONVERT( {fn YEAR(TxnDate)}, SQL_VARCHAR)} = '2009'
This bug has been reported to the QODBC Software Engineer on QSupport Ticket ID: BR00000099. When the fix is made a reply will be posted here. |
|
|
|
|