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

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)

--

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]

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

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