Hi folks,

I'm doing a simple subtraction between two date and I'm getting some weird results.

For example:

SELECT CURRENT_DATE - fecha_pago DAY  FROM cxc_cuota

In EDB Manager I get the correct number in days, but if I do it through Delphi XE2, and get a much, much larger value.
Is this a bug or I'm forgetting to set something?

Currently working on EDB 2.24 b3.

And yes the fecha_pago column is type DATE!

Regards,
Mario

Mario


Since EDBManager is "simply" a Delphi application you should get the same answer. The only thing that occurs to me since I can't remember which Delphi Tim is using to compile EDBManager is its 32 bit vs 64 bit.

If you list CURRENT_DATE as well as the subtraction is that a bigger number in you program than in  EDBManager. If so I don't know the answer but it would point to the problem.

Roy Lambert

Mario



Forget the suggestion of listing CURRENT_DATE - I just realised it was gibberish - brought on by an insufficiency of coffee


Roy Lambert

Mario

I don't often work with Date-time intervals in Delphi with EDB, but I am pretty sure the values are usually milliseconds. i.e. if the interval is 1 day the Delphi value will show as 1 x 24 x 60 x 60 x 1000 = 86,400,000.

When you display in a grid in EDB Manager I think that EDB manager is converting this value into a better format.

I divide the Delphi value by 856,400,000 to convert to Days and by 3,600,000 to get to whole hours.

Mario,

<< I'm doing a simple subtraction between two date and I'm getting some weird results.

For example:

SELECT CURRENT_DATE - fecha_pago DAY  FROM cxc_cuota

In EDB Manager I get the correct number in days, but if I do it through Delphi XE2, and get a much, much larger value.  Is this a bug or I'm forgetting to set something? >>

What you're seeing is the native representation of a day-time interval.  The issue is that Delphi doesn't have TField data types for intervals, so you get 64-bit integer instead.

You can solve this in one of two ways:

1) Like the EDB Manager, by using TField OnGetText/OnSetText event handlers to convert to/from the native representation using these methods:

http://www.elevatesoft.com/manual?action=viewmethod&id=edb2&product=rsdelphi&version=XE&comp=TEDBEngine&method=DayTimeIntervalToSQLStr

http://www.elevatesoft.com/manual?action=viewmethod&id=edb2&product=rsdelphi&version=XE&comp=TEDBEngine&method=SQLStrToDayTimeInterval

2) Just use a CAST() with your SQL:

SELECT CAST(CURRENT_DATE - fecha_pago DAY AS INTEGER)  FROM cxc_cuota

BTW, as long as you're subtracting two dates (and not times or timestamps), you can skip the DAY specifier.

Tim Young
Elevate Software
www.elevatesoft.com