Hello all,

I have the following sql statement constructed...

       sql := 'SELECT
RF_RESULT,RF_PAGES,RF_DURATION,RF_RECEIVEDFROM,RF_DATE,RF_TIME,RF_FAXPHONE
FROM "sftable.dat" ';
       sql := sql + 'WHERE (LOWER(RF_RESULT)=' + QuotedStr('processed') +
') AND (RF_DATE=' + QuotedStr(FormatDateTime(SQLDATEFORMAT,DDate)) + ')';
       sql := sql + ' AND (RF_TIME=' +
QuotedStr(FormatDateTime(SQLTIMEFORMAT,DTime)) + ')' + ' AND (RF_FAXPHONE='
+ QuotedStr(DPhone) + ')';

SQLTIMEFORMAT AND SQLDATEFORMAT are defined as:

const
    SQLDATEFORMAT = 'yyyy-mm-dd';
    SQLTIMEFORMAT = 'hh:mm:ss.zzz am/pm';

DDate and DTime are TDateTime variables

I have one user in particular (perhaps others unreported) who gets this
error:

DBISAM Engine Error # 11949 SQL error - Date, time, or timestamp expression
expected, instead found '04.17.41.629 pm' in WHERE or JOIN clause.

when executing Q.sql(add) then Q.Prepare and Q.Open

Can anyone see the flaw here ?  It appears at first blush that the
FormatDatetime function is not working correctly, but
I'm hard pressed to believe that.  I have checked to make sure that the
SQLTIMEFORMAT constant is only defined once

Stepping through the debugger here on my development system shows the sql
time part formatted as hh:mm:ss.zzz am/pm as it should

Any help would be appreciated

Thanks.

John Taylor

John

If you look at the source for formatdatetime the : symbol in the format
string is replaced with the time separator from regional options.  I guess
the customers time separator is set to a full stop/decimal point.

John

John,

Thank you !

I suspected something to do with regional settings, so I just set up an
'enforcer' function to
force the : symbols where they are supposed to be.  This one bit me in the
butt, as I was
totally not expecting a problem like this


"John Hay" <nospam@nospa.com> wrote in message
news:80B19A01-DDDF-4869-B12B-E29C7E04F244@news.elevatesoft.com...
> John
>
> If you look at the source for formatdatetime the : symbol in the format
> string is replaced with the time separator from regional options.  I guess
> the customers time separator is set to a full stop/decimal point.
>
> John
>

Hi John

SQLTIMEFORMAT = 'hh:mm:ss.zzz am/pm';

This string ist invalid. mm is the Month, but you want the minutes, which
mus be "nn".

SQLTIMEFORMAT = 'hh:nn:ss.zzz am/pm';

In your example the hour 17 is wrong in this 12 hour notation and as such
you get this error.

Regards
Rolf
[Team Elevate]


"John Taylor" <jcta@snappysoftware.com> schrieb im Newsbeitrag
news:9A56D455-4692-4213-B13F-19D800E134C5@news.elevatesoft.com...
> Hello all,
>
> I have the following sql statement constructed...
>
>        sql := 'SELECT
> RF_RESULT,RF_PAGES,RF_DURATION,RF_RECEIVEDFROM,RF_DATE,RF_TIME,RF_FAXPHONE
> FROM "sftable.dat" ';
>        sql := sql + 'WHERE (LOWER(RF_RESULT)=' + QuotedStr('processed') +
> ') AND (RF_DATE=' + QuotedStr(FormatDateTime(SQLDATEFORMAT,DDate)) + ')';
>        sql := sql + ' AND (RF_TIME=' +
> QuotedStr(FormatDateTime(SQLTIMEFORMAT,DTime)) + ')' + ' AND
> (RF_FAXPHONE=' + QuotedStr(DPhone) + ')';
>
> SQLTIMEFORMAT AND SQLDATEFORMAT are defined as:
>
> const
>     SQLDATEFORMAT = 'yyyy-mm-dd';
>     SQLTIMEFORMAT = 'hh:mm:ss.zzz am/pm';
>
> DDate and DTime are TDateTime variables
>
> I have one user in particular (perhaps others unreported) who gets this
> error:
>
> DBISAM Engine Error # 11949 SQL error - Date, time, or timestamp
> expression expected, instead found '04.17.41.629 pm' in WHERE or JOIN
> clause.
>
> when executing Q.sql(add) then Q.Prepare and Q.Open
>
> Can anyone see the flaw here ?  It appears at first blush that the
> FormatDatetime function is not working correctly, but
> I'm hard pressed to believe that.  I have checked to make sure that the
> SQLTIMEFORMAT constant is only defined once
>
> Stepping through the debugger here on my development system shows the sql
> time part formatted as hh:mm:ss.zzz am/pm as it should
>
> Any help would be appreciated
>
> Thanks.
>
> John Taylor

And as John Hay has replied, you must set the local formating temporary to
ANSI formats.

 DateSeparator := '.';
 TimeSeparator := ':';
 TimeAMString := 'am';
 TimePMString := 'pm';

Store the old value before that call to temp vars and after the call back to
the old values.¨

OldDateSeparator := DateSeparator ;
OldTimeSeparator := TimeSeparator ;
OldTimeAMString := TimeAMString ;
OldTimePMString := TimePMString ;
try
 DateSeparator := '.';
 TimeSeparator := ':';
 TimeAMString := 'am';
 TimePMString := 'pm';
 ...... your sql
finally
 DateSeparator := OldDateSeparator ;
 TimeSeparator := OldTimeSeparator ;
 TimeAMString := OldTimeAMString ;
 TimePMString := OldTimePMString ;
end;


"John Taylor" <jcta@snappysoftware.com> schrieb im Newsbeitrag
news:9A56D455-4692-4213-B13F-19D800E134C5@news.elevatesoft.com...
> Hello all,
>
> I have the following sql statement constructed...
>
>        sql := 'SELECT
> RF_RESULT,RF_PAGES,RF_DURATION,RF_RECEIVEDFROM,RF_DATE,RF_TIME,RF_FAXPHONE
> FROM "sftable.dat" ';
>        sql := sql + 'WHERE (LOWER(RF_RESULT)=' + QuotedStr('processed') +
> ') AND (RF_DATE=' + QuotedStr(FormatDateTime(SQLDATEFORMAT,DDate)) + ')';
>        sql := sql + ' AND (RF_TIME=' +
> QuotedStr(FormatDateTime(SQLTIMEFORMAT,DTime)) + ')' + ' AND
> (RF_FAXPHONE=' + QuotedStr(DPhone) + ')';
>
> SQLTIMEFORMAT AND SQLDATEFORMAT are defined as:
>
> const
>     SQLDATEFORMAT = 'yyyy-mm-dd';
>     SQLTIMEFORMAT = 'hh:mm:ss.zzz am/pm';
>
> DDate and DTime are TDateTime variables
>
> I have one user in particular (perhaps others unreported) who gets this
> error:
>
> DBISAM Engine Error # 11949 SQL error - Date, time, or timestamp
> expression expected, instead found '04.17.41.629 pm' in WHERE or JOIN
> clause.
>
> when executing Q.sql(add) then Q.Prepare and Q.Open
>
> Can anyone see the flaw here ?  It appears at first blush that the
> FormatDatetime function is not working correctly, but
> I'm hard pressed to believe that.  I have checked to make sure that the
> SQLTIMEFORMAT constant is only defined once
>
> Stepping through the debugger here on my development system shows the sql
> time part formatted as hh:mm:ss.zzz am/pm as it should
>
> Any help would be appreciated
>
> Thanks.
>
> John Taylor

Thank you Rolf, I completely overlooked that as well.

What I settled on (I think) is

const SQLTIMEFORMAT = 'hh":"nn":"ss"."zzz am/pm' and then replace the am and
pm strings as you indicated.

I believe that will work , do you agree ?

Thanks.

"Rolf Frei" <rolf@eicom.ch> wrote in message
news:EC833442-886D-4628-879D-614D13C29534@news.elevatesoft.com...
> And as John Hay has replied, you must set the local formating temporary to
> ANSI formats.
>
>  DateSeparator := '.';
>  TimeSeparator := ':';
>  TimeAMString := 'am';
>  TimePMString := 'pm';
>
> Store the old value before that call to temp vars and after the call back
> to the old values.¨
>
> OldDateSeparator := DateSeparator ;
> OldTimeSeparator := TimeSeparator ;
> OldTimeAMString := TimeAMString ;
> OldTimePMString := TimePMString ;
> try
>  DateSeparator := '.';
>  TimeSeparator := ':';
>  TimeAMString := 'am';
>  TimePMString := 'pm';
>  ...... your sql
> finally
>  DateSeparator := OldDateSeparator ;
>  TimeSeparator := OldTimeSeparator ;
>  TimeAMString := OldTimeAMString ;
>  TimePMString := OldTimePMString ;
> end;
>
>
> "John Taylor" <jcta@snappysoftware.com> schrieb im Newsbeitrag
> news:9A56D455-4692-4213-B13F-19D800E134C5@news.elevatesoft.com...
>> Hello all,
>>
>> I have the following sql statement constructed...
>>
>>        sql := 'SELECT
>> RF_RESULT,RF_PAGES,RF_DURATION,RF_RECEIVEDFROM,RF_DATE,RF_TIME,RF_FAXPHONE
>> FROM "sftable.dat" ';
>>        sql := sql + 'WHERE (LOWER(RF_RESULT)=' + QuotedStr('processed') +
>> ') AND (RF_DATE=' + QuotedStr(FormatDateTime(SQLDATEFORMAT,DDate)) + ')';
>>        sql := sql + ' AND (RF_TIME=' +
>> QuotedStr(FormatDateTime(SQLTIMEFORMAT,DTime)) + ')' + ' AND
>> (RF_FAXPHONE=' + QuotedStr(DPhone) + ')';
>>
>> SQLTIMEFORMAT AND SQLDATEFORMAT are defined as:
>>
>> const
>>     SQLDATEFORMAT = 'yyyy-mm-dd';
>>     SQLTIMEFORMAT = 'hh:mm:ss.zzz am/pm';
>>
>> DDate and DTime are TDateTime variables
>>
>> I have one user in particular (perhaps others unreported) who gets this
>> error:
>>
>> DBISAM Engine Error # 11949 SQL error - Date, time, or timestamp
>> expression expected, instead found '04.17.41.629 pm' in WHERE or JOIN
>> clause.
>>
>> when executing Q.sql(add) then Q.Prepare and Q.Open
>>
>> Can anyone see the flaw here ?  It appears at first blush that the
>> FormatDatetime function is not working correctly, but
>> I'm hard pressed to believe that.  I have checked to make sure that the
>> SQLTIMEFORMAT constant is only defined once
>>
>> Stepping through the debugger here on my development system shows the sql
>> time part formatted as hh:mm:ss.zzz am/pm as it should
>>
>> Any help would be appreciated
>>
>> Thanks.
>>
>> John Taylor
>
>

Yes is OK, but setting the DateSeparator and TimeSeparator looks more
professional to me.

Regards
Rolf
[Team Elevate]


"John Taylor" <jcta@snappysoftware.com> schrieb im Newsbeitrag
news:AC39FA9A-0192-46D0-911A-BA92A04C0EB3@news.elevatesoft.com...
> Thank you Rolf, I completely overlooked that as well.
>
> What I settled on (I think) is
>
> const SQLTIMEFORMAT = 'hh":"nn":"ss"."zzz am/pm' and then replace the am
> and pm strings as you indicated.
>
> I believe that will work , do you agree ?
>
> Thanks.
>
> "Rolf Frei" <rolf@eicom.ch> wrote in message
> news:EC833442-886D-4628-879D-614D13C29534@news.elevatesoft.com...
>> And as John Hay has replied, you must set the local formating temporary
>> to ANSI formats.
>>
>>  DateSeparator := '.';
>>  TimeSeparator := ':';
>>  TimeAMString := 'am';
>>  TimePMString := 'pm';
>>
>> Store the old value before that call to temp vars and after the call back
>> to the old values.¨
>>
>> OldDateSeparator := DateSeparator ;
>> OldTimeSeparator := TimeSeparator ;
>> OldTimeAMString := TimeAMString ;
>> OldTimePMString := TimePMString ;
>> try
>>  DateSeparator := '.';
>>  TimeSeparator := ':';
>>  TimeAMString := 'am';
>>  TimePMString := 'pm';
>>  ...... your sql
>> finally
>>  DateSeparator := OldDateSeparator ;
>>  TimeSeparator := OldTimeSeparator ;
>>  TimeAMString := OldTimeAMString ;
>>  TimePMString := OldTimePMString ;
>> end;
>>
>>
>> "John Taylor" <jcta@snappysoftware.com> schrieb im Newsbeitrag
>> news:9A56D455-4692-4213-B13F-19D800E134C5@news.elevatesoft.com...
>>> Hello all,
>>>
>>> I have the following sql statement constructed...
>>>
>>>        sql := 'SELECT
>>> RF_RESULT,RF_PAGES,RF_DURATION,RF_RECEIVEDFROM,RF_DATE,RF_TIME,RF_FAXPHONE
>>> FROM "sftable.dat" ';
>>>        sql := sql + 'WHERE (LOWER(RF_RESULT)=' + QuotedStr('processed')
>>> + ') AND (RF_DATE=' + QuotedStr(FormatDateTime(SQLDATEFORMAT,DDate)) +
>>> ')';
>>>        sql := sql + ' AND (RF_TIME=' +
>>> QuotedStr(FormatDateTime(SQLTIMEFORMAT,DTime)) + ')' + ' AND
>>> (RF_FAXPHONE=' + QuotedStr(DPhone) + ')';
>>>
>>> SQLTIMEFORMAT AND SQLDATEFORMAT are defined as:
>>>
>>> const
>>>     SQLDATEFORMAT = 'yyyy-mm-dd';
>>>     SQLTIMEFORMAT = 'hh:mm:ss.zzz am/pm';
>>>
>>> DDate and DTime are TDateTime variables
>>>
>>> I have one user in particular (perhaps others unreported) who gets this
>>> error:
>>>
>>> DBISAM Engine Error # 11949 SQL error - Date, time, or timestamp
>>> expression expected, instead found '04.17.41.629 pm' in WHERE or JOIN
>>> clause.
>>>
>>> when executing Q.sql(add) then Q.Prepare and Q.Open
>>>
>>> Can anyone see the flaw here ?  It appears at first blush that the
>>> FormatDatetime function is not working correctly, but
>>> I'm hard pressed to believe that.  I have checked to make sure that the
>>> SQLTIMEFORMAT constant is only defined once
>>>
>>> Stepping through the debugger here on my development system shows the
>>> sql time part formatted as hh:mm:ss.zzz am/pm as it should
>>>
>>> Any help would be appreciated
>>>
>>> Thanks.
>>>
>>> John Taylor
>>
>>
>

Rolf,

> SQLTIMEFORMAT = 'hh:mm:ss.zzz am/pm';
>
> This string ist invalid. mm is the Month, but you want the minutes, which
> mus be "nn".
>
> SQLTIMEFORMAT = 'hh:nn:ss.zzz am/pm';
>
> In your example the hour 17 is wrong in this 12 hour notation and as such
> you get this error.

17 is the minutes.  Although the documentation of the formatdatetime
requires "nn" for minutes, if you look at the source "mm" also works fine if
it is preceded by "hh".

Cheers

John