Group search for min/max date field

rbuser

Hi,

I'm trying to generate the equivalent of following query using the RB query
designer:

select custnum, max(transdate) from cust
join invoice on cust.custnum = invoice.custnum
group by custnum
having max(transdate) <= DATE '2013-01-01'


After building the query in the RB query designer, it generates the
following SQL:


SELECT cust.CustNum,
MAX(invoice.TransDate) MAX_invoice_TransDate
FROM cust cust INNER JOIN invoice invoice ON
(invoice.CustNum = cust.CustNum)
GROUP BY cust.CustNum
HAVING ( MAX(invoice.TransDate) <= )

In short, it's missing the comparison value and closing parenthesis if the
group search criteria value is entered directly in the value column of the
query designer. But it does generate the proper query when using a
parameter. It also works properly for VarChar or Numerical fields, just
not date fields.

Any chance we can get a patch for this so that manually entered values will
work with min/max values on a date field?

Details:
Report Builder Version: 14.08
Delphi Version: XE
Database: ElevateDB


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

nicocizik

Hi Terry,

What format are you using to enter the date? Dade expects the standard
machine locale-defined date format when entering dates into a query.

For instance, if your machine is set up display dates as mm/dd/yyyy, you
would need to enter the date as 01/01/2013. This would then get
converted to a TDateTime type and then converted again to the yyyy-mm-dd
format that ElevateDB expects according to the specifications in the
daElevateDB.pas file.

http://www.digital-metaphors.com/rbWiki/DADE/Fundamentals/Date_Formatting

I created a quick test on my machine with Delphi XE and the latest
version of ElevateDB and this functioned correctly.

rbuser

Hi Nico,


01/05/2013 for Jan 1st 2013, which is what the system was set to for the
date format (MM/dd/yyyy).



Looks like it was a glitch in my Windows settings. I've seen some issues
with Windows 7 and Delphi apps not using the selected date format, and it
appears that I ran into it as well. I had to change and apply the windows
language and region to something other than what it was set to, change them
back and apply the settings. Once I did that, the group search started
working as expected.

Thank you for taking the time to verify this and I'm sorry to have wasted
your time.

---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------