Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
DBISAM conversion & INTERVALs |
Sun, May 1 2011 1:29 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Been giving this some thought. Haven't written any code yet because I'm still trying to get my head around how to do it.
My latest thoughts are: 1. Only try and do anything in the WHERE clause 2. Only bother if there is an integer in the expression (an expression is either the clause or the bit between ANDs or ORs) and there is either a plus or minus operator The approach would be to each CAST column or CURRENT_xx to a VARCHAR, wrap in a UDF which would convert to float so something like DateDone BETWEEN Current_Date - 120 AND Current_Date - 90 would become DateAsFloat(CAST(DateDone AS VARCHAR)) BETWEEN DateAsFloat(CAST(Current_Date AS VARCHAR)) - 120 AND DateAsFloat(CAST(Current_Date AS VARCHAR)) - 90 It looks a bit horrendous but it should cope regardless of the columns being date, timestamp, integer, or decimal. It should be relatively easy to write a DateAsFloat function in Delphi to detect what the string passed in was and convert to a float. No idea what it would do to performance of a query/script though. I'd appreciate comments (especially from the two Adams) about wether this is worth it or INTERVALs should just be a bit of hand conversion for the scripts. Roy Lambert |
Sun, May 1 2011 5:33 PM | Permanent Link |
Adam Brett Orixa Systems | Thanks for this Roy. Not sure but it seems heavy handed.
I have recoded: WHERE DateDone BETWEEN Current_Date - 120 AND Current_Date - 90 as: WHERE DateDone BETWEEN Current_Date - INTERVAL '120' DAY AND Current_Date - INTERVAL '90' DAY This works & is easy to read. Writing a SCRIPT to STRINGREPLACE and swap over between the 2 is not too hard either. ... I get the issue you are identifying that DateDone might already be an integer or float ... if it is a calculation or a date range that has been passed in ... but I think that sort of SQL complexity is pretty rare in DBISAM! I had another issue transferring from DBISAM to EDB where your method _was_ useful though: WHERE DateDone > '%s' ... I was passing in date-as-string (using ANSIDateToStr()) ... This I have had to change to: WHERE DateDone > CAST('%s' as DATE) or WHERE DateDone > CAST('%s' as TIMESTAMP) -- |
Mon, May 2 2011 2:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>WHERE DateDone BETWEEN Current_Date - INTERVAL '120' DAY AND Current_Date - INTERVAL '90' DAY > >This works & is easy to read. Writing a SCRIPT to STRINGREPLACE and swap over between the 2 is not too hard either. That is the correct syntax >.. I get the issue you are identifying that DateDone might already be an integer or float ... if it is a calculation or a date range that has been passed in ... but I think that sort of SQL complexity is pretty rare in DBISAM! Sort of - its that there's no way to tell from the text wether or not a particular piece of arithmetic is using dates or numbers. With your own SQL you'll (hopefully) know enough to say wether you use only date arithmetic in which case it becomes trivial to detect and convert to the proper syntax but a general purpose converter can't make those assumptions hence the roundabout method. >I had another issue transferring from DBISAM to EDB where your method _was_ useful though: > >WHERE DateDone > '%s' > >.. I was passing in date-as-string (using ANSIDateToStr()) ... > >This I have had to change to: > >WHERE DateDone > CAST('%s' as DATE) > >or > >WHERE DateDone > CAST('%s' as TIMESTAMP) > Why not convert to WHERE DateDone > DATE '%s' ? Roy Lambert [Team Elevate] |
Wed, May 4 2011 4:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
This is the logic that DBISAM uses to determine if a string constant is a date or not: procedure TExpressionVerifier.VerifyDateTimeLiteral(LeftToken: TExpToken; RightToken: TExpToken); begin if (LeftToken.DataType in [TYPE_DATE,TYPE_TIME,TYPE_TIMESTAMP]) and ((RightToken.TokenType=etCONST) and (RightToken.DataType=TYPE_ZSTRING)) then begin if not ConvertStringToDateTime(RightToken.Value, LeftToken.DataType) then ExpectedTypesError(RightToken,[TYPE_DATE,TYPE_TIME,TYPE_TIMESTAMP],[SUBTYPE_UNKNOWN]); RightToken.DataType:=LeftToken.DataType; RightToken.DataSize:=LeftToken.DataSize; RightToken.DataDecimals:=LeftToken.DataDecimals; end; end; As you can see, it simply looks at the left-side of the binary expression, and if it is a date/time, then it tries to convert the string to a date/time value. If that fails, then it issues an error. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, May 5 2011 2:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
That's the sort of thing I was thinking about, but I was trying to avoid an error I'm now waiting for after version 3 and your kind offer of a converter. Roy Lambert |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |