[DUG] firebird query parameters behaving oddly

[Mike Stokes]

Hello Tracey,

I use FireBird all the time and it works fine if you know how to use it.

For simple date time conversions check the ShortDateFormat := 
'dd/mm/yyyy'; before converting StrToDateDef(MyDate, 0);

Also in FireBird if you want to check between a date range use something 
like the following

select * from mytable
where DateFrom = :DateFrom
and DateTo = :DateTo

Query1.ParamByName('DateFrom').asDateTime := trunc(MyDate);
Query1.ParamByName('DateTo').asDateTime := trunc(SomeOtherDate) + 1;

This always works for me.

All the best,
Mike

Tracey wrote:
> Unfortunately, I am stuck here, as my app is a cgi/isapi web app that is
> passing the date value as a string.  Even if I convert '10/11/2006' back to
> a date from there, the conversion results in '11/10/2006'
>
>  
>
> -----Original Message-----
> From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz] On
> Behalf Of Robert martin
> Sent: Friday, 6 July 2007 4:35 p.m.
> To: NZ Borland Developers Group - Delphi List
> Subject: Re: [DUG] firebird query parameters behaving oddly
>
> Hi
>
> If you never use dates as strings in your queries (i.e. always use 
> asDate or AsDateTime) you should never get locale issues.  Its one of 
> the biggest reasons to always use ParamByName instead of building SQL 
> strings IMO.
>
>
> Rob Martin
> Software Engineer
>
> phone +64 03 377 0495
> fax   +64 03 377 0496
> web www.chreos.com
>
> Wild Software Ltd
>
>
>
> Tracey wrote:
>   
>> Hmmm actually I think It has something to do with mysterious locale issues
>> on my new laptop.. I had all sorts of date issues with mssql and Reporting
>> services too.  
>>
>>
>>
>> -----Original Message-----
>> From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz]
>>     
> On
>   
>> Behalf Of Edward Koryagin
>> Sent: Friday, 6 July 2007 3:54 p.m.
>> To: NZ Borland Developers Group - Delphi List
>> Subject: Re: [DUG] firebird query parameters behaving oddly
>>
>> time part of timestamp datatype in Firebird?
>>
>> --- Tracey <tracey at logis.co.nz> wrote:
>>
>>   
>>     
>>> Hi all
>>>
>>>  
>>>
>>> I have in my code many areas where queries are
>>> "strung" together then
>>> executed.  These queries are parameterized.  However
>>> I am having problems
>>> with my date parameters.
>>>
>>>  
>>>
>>> If I have my sql string looking like this:
>>>
>>>  
>>>
>>> SQL.Add('Select stuff');
>>>
>>> SQL.Add('From tables ');
>>>
>>> SQL.Add('Join more tables on keys');
>>>
>>> SQL.Add('Where DataDate >= :from');
>>>
>>> SQL.Add('And DataDate <= :to');
>>>
>>> ParamByName('from').AsString = sFromDat; 
>>> (previously checked valid date
>>> loaded into string)
>>>
>>> ParamByName('to).AsString = sToDat;  (previously
>>> checked valid date loaded
>>> into string)
>>>
>>>  
>>>
>>>  
>>>
>>> These queries work, however I seem to be missing
>>> records.  
>>>
>>>  
>>>
>>> If I do the unthinkable and string the date values
>>> in with the query:
>>>
>>>  
>>>
>>> SQL.Add('Select stuff');
>>>
>>> SQL.Add('From tables ');
>>>
>>> SQL.Add('Join more tables on keys');
>>>
>>> SQL.Add('Where DataDate >= ''' + sFromDat + '''');
>>>
>>> SQL.Add('And DataDate <= ''' + sToDat + '''');
>>>
>>>  
>>>
>>> I get the correct amount of records
>>>
>>>  
>>>
>>> I wouldn't even know where to start on this one, I
>>> am leaving the params as
>>> they are in the working code, but not happy about
>>> it.
>>>
>>>  
>>>
>>> Any revelations?
>>>
>>>  
>>>
>>> Tracey
>>>
>>>  
>>>
>>>     
>>>       
>>>> _______________________________________________
>>>>       
>>>>         
>>> NZ Borland Developers Group - Delphi mailing list
>>> Post: delphi at delphi.org.nz
>>> Admin: http://delphi.org.nz/mailman/listinfo/delphi
>>> Unsubscribe: send an email to
>>> delphi-request at delphi.org.nz with Subject:
>>>     
>>>       
>> unsubscribe
>>
>>
>> Edward Koryagin
>>
>> Send instant messages to your online friends http://au.messenger.yahoo.com
>>     
>
>   
>> _______________________________________________
>> NZ Borland Developers Group - Delphi mailing list
>> Post: delphi at delphi.org.nz
>> Admin: http://delphi.org.nz/mailman/listinfo/delphi
>> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
>> unsubscribe
>>
>>
>> _______________________________________________
>> NZ Borland Developers Group - Delphi mailing list
>> Post: delphi at delphi.org.nz
>> Admin: http://delphi.org.nz/mailman/listinfo/delphi
>> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
>>     
> unsubscribe
>   
>>   
>>     
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
> unsubscribe
>
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject: unsubscribe
>
>
>
>