I just switched from JDBC-ODBC to UCanAccess as of version 2.0.7 and my java program is giving me errors to do with the use of my DateDiff function. Is there perhaps some sort of different sql syntax I should be using? Might this be related to a problem with translating calculations in the driver?
Here's the sql statement, straight out of my program: select * from Screens where datediff('d',ScreenDate,'"+Tools.getDate()+"') between 0 and " + num + " order by ScreenDate desc, ShiftID, ScreenID
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Savva,
the issue seems to be due to the date format you're using, which likely isn't yet supported by UCanAccess.
Here you can find some example of DateDiff calls (see the sql string and date formatting, not at my junit test methods): https://sourceforge.net/p/ucanaccess/code/HEAD/tree/ucanaccess/trunk/src/test/java/net/ucanaccess/test/FunctionsTest.java#l373
UCanAccess supports the Access # symbol as date delimiter, you're using a single quote ' instead.
select * from Screens where datediff('d',ScreenDate,#"+Tools.getDate()+"#) between 0 and " + num + " order by ScreenDate desc, ShiftID, ScreenID
It would be great if you could send me an example of the complete sql string that your program generates (after having printed it, I don't know what's the Tools.getDate() output).
Cheers Marco
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Okey, I've understood: it isn't an issue about the date formatting.
Access supports both dateDiff(string, string,string) and dateDiff(string,date,date)
while UCanAccess only supports dateDiff(string,date,date).
If you call, as you're doing , dateDiff(string, string,string) Access converts the two latest arguments into date objects.
mmmm... I could extend my support to dateDiff(string, string,string), I'm analyzing...
In the meantime, the following sql should work:
select * from Screens where datediff('d',ScreenDate,#"+Tools.getDate()+"#) between 0 and " + num + " order by ScreenDate desc, ShiftID, ScreenID
Last edit: Marco Amadei 2014-07-13
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Overloading of all "date functions" in svn trunk, so that they can accept a string (VARCHAR) instead of a date as argument. Nevertheless keep in mind that this kind of calls forces a parsing operation, with a result which depend on the regional settings (locale), so you had better use the # delimeters and pass a date instead of a string.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I just switched from JDBC-ODBC to UCanAccess as of version 2.0.7 and my java program is giving me errors to do with the use of my DateDiff function. Is there perhaps some sort of different sql syntax I should be using? Might this be related to a problem with translating calculations in the driver?
Here's the sql statement, straight out of my program: select * from Screens where datediff('d',ScreenDate,'"+Tools.getDate()+"') between 0 and " + num + " order by ScreenDate desc, ShiftID, ScreenID
Hi Savva,
the issue seems to be due to the date format you're using, which likely isn't yet supported by UCanAccess.
Here you can find some example of DateDiff calls (see the sql string and date formatting, not at my junit test methods):
https://sourceforge.net/p/ucanaccess/code/HEAD/tree/ucanaccess/trunk/src/test/java/net/ucanaccess/test/FunctionsTest.java#l373
UCanAccess supports the Access # symbol as date delimiter, you're using a single quote ' instead.
select * from Screens where datediff('d',ScreenDate,#"+Tools.getDate()+"#) between 0 and " + num + " order by ScreenDate desc, ShiftID, ScreenID
It would be great if you could send me an example of the complete sql string that your program generates (after having printed it, I don't know what's the Tools.getDate() output).
Cheers Marco
Okey, I've understood: it isn't an issue about the date formatting.
Access supports both dateDiff(string, string,string) and dateDiff(string,date,date)
while UCanAccess only supports dateDiff(string,date,date).
If you call, as you're doing , dateDiff(string, string,string) Access converts the two latest arguments into date objects.
mmmm... I could extend my support to dateDiff(string, string,string), I'm analyzing...
In the meantime, the following sql should work:
select * from Screens where datediff('d',ScreenDate,#"+Tools.getDate()+"#) between 0 and " + num + " order by ScreenDate desc, ShiftID, ScreenID
Last edit: Marco Amadei 2014-07-13
Overloading of all "date functions" in svn trunk, so that they can accept a string (VARCHAR) instead of a date as argument. Nevertheless keep in mind that this kind of calls forces a parsing operation, with a result which depend on the regional settings (locale), so you had better use the # delimeters and pass a date instead of a string.