Board index » delphi » Help with BDE and SQL Update Dates in Paradox Tables

Help with BDE and SQL Update Dates in Paradox Tables

Hi all!!

I've got this wee bit of a problem with date formats.

Basically, I've got an SQL Update Statement on a Paradox table which updates a
date field. Somehow, It isn't using the correct D/M/Y formats and throws a
Type Mismatch.

In Update:

        "Update table set date_entry = "20/4/1997'

My Regional settings are set to DD/MM/YYYY

and the BDE Date mode is set to 0. But I chaged it to 1... but same prob.

The date is displayed correctly when querried.... ie DD/MM/YYYY... but BDE
insists I use MM/DD/YYYY when updating or inserting...

Btw, Using BDE 3.5....

Has anyone come accross this before? And is there a way around this without
resorting to hard coding date formats?

I would appreciate any of your feedbacks on this and thanks in advance!!

I would also be very thankfull if you could CC to azi...@gto.net.om  as
well....:))

Naz.

 

Re:Help with BDE and SQL Update Dates in Paradox Tables


hi,

paradox uses the date format defined in the BDE .i wrote a simple unit that
helps me to fromat a date string to the correct format needed by the BDE :

----------------------------------------------------------------------------
--------
unit BDEUtil;

interface

  Procedure ReadBDEConfig;
  Function FormatSQLDate(d : TDateTime) : String;

implementation
uses
  Classes, DBTables, SysUtils, DphiUtil;

Type
  TDateOrder = ( doMDY, doDMY, doYMD);
  TBDEDateSettings = record
    Order : TDateOrder;
    Separator : char;
    FourDigitsYear : Boolean;
    sFormat : String;
  end;

var
  glbBDEDate : TBDEDateSettings;

Procedure ReadBDEConfig;
var
  Entries : TStringList;
  YearFormat : String[4];
begin
  try
    if Assigned(Session) then
    begin
       Entries := TStringList.Create;
       Session.GetConfigParams('\SYSTEM\FORMATS\DATE','',Entries);
       glbBDEDate.Order := TDateOrder(StrToIntDef(Entries.Values['MODE'],
0));
       glbBDEDate.Separator := (Entries.Values['SEPARATOR'])[1];
       glbBDEDate.FourDigitsYear := (Entries.Values['FOURDIGITYEAR'] =
'TRUE');
       {build the BDE/SQL Date Format String}
       with glbBDEDate do
       begin
          if FourDigitsYear then
             YearFormat := 'yyyy'
          else
             YearFormat := 'yy';

          case Order of
          doMDY : sFormat := 'mm' + Separator + 'dd' + Separator +
YearFormat;
          doDMY : sFormat := 'mm' + Separator + 'dd' + Separator +
YearFormat;
          doYMD : sFormat := YearFormat + Separator + 'mm' + Separator +
'dd';
          end;

       end;
    end;
  Except
    on e: Exception do
       GenExceptMsg(e, 'ReadBDEConfig');
  end;
end;

Function FormatSQLDate(d : TDateTime) : String;
begin
  Result := FormatDateTime(glbBDEDate.sFormat, d);
end;

end.

----------------------------------------------------------------------------
-------------------

======================================
MIND CTI ltd.
Computer Telephony Software

POB 144, Yoqneam Illit 20692, ISRAEL.
Tel: +972-4-993-7773  
Fax: +972-4-993-7776
mailto:r...@mind.co.il

Visit our site: http:\\www.mind.co.il
======================================
            Keep us in mind !!
======================================

Nazar Aziz <azi...@gto.net.om.nofukspam> wrote in article
<5vfggs$...@snews2.zippo.com>...

Quote

> Hi all!!

> I've got this wee bit of a problem with date formats.

> Basically, I've got an SQL Update Statement on a Paradox table which
updates a
> date field. Somehow, It isn't using the correct D/M/Y formats and throws
a
> Type Mismatch.

> In Update:

>         "Update table set date_entry = "20/4/1997'

> My Regional settings are set to DD/MM/YYYY

> and the BDE Date mode is set to 0. But I chaged it to 1... but same prob.

> The date is displayed correctly when querried.... ie DD/MM/YYYY... but
BDE
> insists I use MM/DD/YYYY when updating or inserting...

> Btw, Using BDE 3.5....

> Has anyone come accross this before? And is there a way around this
without
> resorting to hard coding date formats?

> I would appreciate any of your feedbacks on this and thanks in advance!!

> I would also be very thankfull if you could CC to azi...@gto.net.om  as
> well....:))

> Naz.

Other Threads