Date manipulation

[mjsof84]

Hi guys,

I'm looking for a way to get today's date in delphi and then calculate the date 7 days ago from it.

I then want to search through a TQuery using SQL to find any records which have a date field older than 7 days.

The SQL seems simple enough...but can someone tell me the simplest way of getting the date in delphi and manipulating it?

cheers..

 

[colttaylor]

s := datetostr(date-7);
If it's stupid but it works, it isn't stupid

 

[mjsof84]

ah, thanks for that.

what i've got so far is this..

--------------------
procedure TForm1.MyProcedure;
var datey: string;
begin
datey := datetostr(date-7);
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add ('SELECT First_Name, Last_Name, Date_Joined');
Query1.SQL.Add ('FROM member');
Query1.SQL.Add ('WHERE Date_Joined BETWEEN 27/03/2003 AND' + datey);
Query1.Open;

end;
--------------------

OK, I know the WHERE statement is wrong, but i can't seem to find the correct format anywhere on the web..)i'm using a paradox 7 database)

any help appreciated...

 

[colttaylor]

Ok,
I'm not that good with SQL either and the various flavors attached to each database makes things even more complex. I would just try to spread out the logic alittle and skip the between statement. For example...

s := 'SELECT First_Name, Last_Name, Date_Joined'
+ ' FROM member WHERE'
+ ' (DateJoined > 27/03/2003) AND'
+ ' (DateJoined < ' + datey + ')';
Query1.SQL.Add (s);

...Also, I would build the query in a string as I did above so that I can look at the finished query in the debugger. I think your assembled query might have been missing a few embedded spaces.

Just a comment...
I read back over my previous post and realize that in this case, my standard footer/signature line might have sounded insulting. I am always recommending low-tech solutions such as the one above. I prefer to know how to use a little bit of technical skill creatively, than to try to know every bit of every technology. &quot;Between&quot; statements are a good example. My footer simply summarizes this programming-theology, but in the context of very short replies like the one I made earlier, it does read a little like an insult. Sorry for any implied criticism and welcome to the Delphi world!
Peace,
Colt. If it's stupid but it works, it isn't stupid

 

[awarnica]

Re this line:

Query1.SQL.Add ('WHERE Date_Joined BETWEEN 27/03/2003 AND' + datey);

you will need a space after the AND:

Query1.SQL.Add ('WHERE Date_Joined BETWEEN 27/03/2003 AND ' + datey);

You may or may not need quotes around the dates, too, depending on the database:

Query1.SQL.Add ('WHERE Date_Joined BETWEEN ''' + '27/03/2003' + ''' AND ''' + datey + '');