Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Subtraction between dates results in weird values... |
Fri, Oct 28 2016 3:36 PM | Permanent Link |
Mario Enríquez Open Consult | 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 |
Sat, Oct 29 2016 2:44 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sat, Oct 29 2016 2:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mario
Forget the suggestion of listing CURRENT_DATE - I just realised it was gibberish - brought on by an insufficiency of coffee Roy Lambert |
Tue, Nov 1 2016 11:11 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Tue, Nov 1 2016 2:52 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |