asked on

SQL DateTime Comparison

I want to select records with a time stamp of (now - n*hours). I'm using D3 and Paradox tables. Here's what I've done that doesn't work:

q is tquery
lastplay is tdatetime
n is integer

q.Close;
q.SQL.Clear;
q.SQL.Add('select * from tracks where lastplay < :param');

q.ParamByName('param').AsDateTime := now - n/24;
q.Open;

what am i missing? it must be something simple.

kretzschmar

? rwv,

where is the problem?
have done just a test and it works properly

sample code

//query holds : select * from time_db where dt < :param
procedure TForm1.Button1Click(Sender: TObject);
begin
query1.Close;
query1.ParamByName('param').AsDateTime := now - (strtoint(edit1.text)/24);
query1.Open;
end;

do you get an errormessage?

meikl

wimmeyvaert

Hi rwv,

What error do you get ?

Regards,
The Mayor

Felixin

Hi, RWY and all of you

Are you sure that the conversion to hours of N/24 is OK?

Why don't you use

function EncodeTime(Hour, Min, Sec, MSec: Word): TDateTime;

calling it like this

EncodeTime (MyNumberOFHours, 0, 0, 0);

The problem is that I don't understand what you mean by "It doesn't work". Do you have any kind of error message or, simply, you don't get the result that you where expecting?

Best regards

kretzschmar

felixin
n/24 is ok
meikl

Igor UL7AAjr

listening...

rwv

ASKER

no error, i'm just not getting the result i expect.
my sql statement is a little more complex. i'll look it over more closely and let you know.

Igor UL7AAjr

rwv,
can you show us your SQL and table structures? It will easy to help you.
-----
Igor.

rwv

ASKER

I've simplified my query and it still isnt working right.

I'm using the var tn just to look at the values at a break point, they seem to be ok.

I've discovered something curious:
When set the value of the table's field (lastplay) to now() with an update query, the select query fails to exclude this record.
However, when I edit this record in a dbgrid, by only retyping a character of the date string, and then post it, the query works as expected.

Any ideas?

The only field below that really matters is lastplay which is a tDateTime.

function TfrmJBox.TrackQuery(cdid : integer) : boolean;
var ok : boolean;
qwhere : string;
tn : tdatetime;
begin
ok := true;

qwhere := 'where (cdid ='+inttostr(cdid)+') and ((lastplay < :dtlastplay) or (lastplay is null)) ';

try
if qTracks.State <> dsInactive then
qTracks.Close;
qTracks.SQL.Clear;
qTracks.SQL.Add('select '+
'trackno, tracklen, title, votes, points, plays, lastplay, id, artist '+
'from "'+gDataDir+'\tracks.db" '+
qwhere+
'order by trackno');
tn := now;
qTracks.ParamByName('dtlastplay').AsDateTime := tn - gMinReplayTime/24;
qTracks.Open;
except
ok := false;
end;
TrackQuery := ok;
end;

ASKER CERTIFIED SOLUTION

Igor UL7AAjr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwv

ASKER

Igor,
I didnt change datetime format, but you did lead me to the solution:
I was using
' set lastplay = "'+DateTimeToStr(now)+'"'
changed it to
' set lastplay = :paramNow'
then AddParamByName('paramNow').AsDateTime

However, I dont understand why this makes a difference. Aren't Tdatetime types just numbers once they are stored?

Thanks all for your help to everyone.

Bobby Valentine

rwv

ASKER

correction
' set lastplay = :paramNow'
ParamByName('paramNow').AsDateTime := now;