Date Function Problem 2

[FerrieC]

Hey there..

Was wondering if anyone could help,

In my system - for all orders that are older than 3 months i want to sign them off..

so for exmple I have a date in table 1 and i want to loop round that table until i find a date older than 3 months, then i want to set the values of some fields.

Any ideas how i can do this???

Thanks for your help in advance

FerrieC

 

[hilfy]

You can use the IncMonth function to add or subtract months from a date.

Table1.First;
d := IncMonth(Date, -3)
While not Table1.eof do
begin
  if Table1.FieldByName('MonthField').AsDateTime < d then
  begin
    //update the other fields...
  end;
  Table1.Next;
end;

There is probably also a way to do it in SQL, depending on which database and set of data access components you're using.

-Dell

 

[lespaul]

why loop through the whole table? Drop an SQL component on the form and use an UPDATE SQL statement:

SQL.Clear;
SQL.Add('UPDATE TblName SET FIELD1 = ''Something'', Field2 = ''Nothing'', Field3 = ''Everything'' WHERE SomeDateField < CutOffDate');
ExecSQL;

Leslie

 

[FerrieC]

I am looking to sign off orders older than 3 months.. so using SQL how do you set the cut off date?

Ps in the code above are you hard coding that or did you sugest putting it in the strings part of the component

Thanks

 

[FerrieC]

Dell what type of variable do you set D to ?

 

[towerbase]

Unfortunately, different database systems use slightly different versions of SQL. This is particularly true when it comes to dates. You don't specify which database you are using (which isn't very helpful).

I have experience of using Paradox (okay for simple, small standalone systems) and MySQL (good for large multi user systems).

In Paradox, SQL dates are in 'mm/dd/yyyy' format. In MySQL, dates are in 'yyyy-mm-dd' format.

So if you are using Paradox, you would compute the date 3 months ago and create the SQL statement with something like this clarification of the previous examples:

var
  d: TDateTime;
  dstring: string;
begin
  ...
  d := d := IncMonth(Date, -3);
  dstring := FormatDateTime ( 'mm/dd/yyyy', d );
  ...
  SQL.Clear;
  SQL.Add ( 'UPDATE tblname' +
            ' SET field1 = ' + QuotedStr('Something') +
            ', field2 = ' + QuotedStr('Nothing') +
            ', field3 = ' + QuotedStr('Everything') +
            ' WHERE somedatefield < ' + dstring );
  ExecSQL;

The code for a MySQL DBMS would look very similar but the FormatDateTime statement would look like:

  dstring := FormatDateTime ( 'yyyy-mm-dd', d );

You ask about not hard coding in values which is a good question. A good way to resolve this problem is to use Params. Have a look at the Delphi Help for Params relating to TQuery.

Andrew
Hampshire, UK

 

[lespaul]

Much better explanation than mine Andrew, have a star!

I actually use the QuotedStr alot, but it was just TOO EARLY!!!

les

 

[FerrieC]

Sorry for such an bad senario..

thanks for all your help.. just to let you know i am using DBase

 

[hilfy]

FerrieC,

d is a TDateTime.

-Dell

 

[hilfy]

Also, since you're using dBase tables, if you're not using the Advantage server, I don't know that SQL will be much faster than looping through the code because the SQL will all be processed on the local workstation anyway. And there's an easier way to pass the date to the SQL by using a parameter (I know this works with P'dox, but not sure about xBase.)

In the IDE, set the SQL to something like:

Update tblname
Set Field1 = :Field1val,
    Field2 = :Field2Val
where DateField = :DateVal

Your code would then look like:

var
  d: TDateTime;
begin
  ...
  d := d := IncMonth(Date, -3);
  ...
  with MyQuery do
  begin
    ParamByName('Field1val').AsString := 'Somevalue';
    ParamByName('Field2val').AsInteger := 1;
    ParamByName('DateVal').AsDateTime := d;
    ExecSQL;
  end;

You need to be sure to set the DataType (ftString, ftDateTime, ftInteger, etc.) and the ParamType (ptIn) of the parameters in the Object Inspector or in code prior to setting the parameter values.

The advantage to doing this is that you don't have to convert all of the various values to strings to get the query to work.

-Dell