rwv
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').AsD ateTime := now - n/24;
q.Open;
what am i missing? it must be something simple.
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').AsD
q.Open;
what am i missing? it must be something simple.
Hi rwv,
What error do you get ?
Regards,
The Mayor
What error do you get ?
Regards,
The Mayor
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
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
felixin
n/24 is ok
meikl
n/24 is ok
meikl
listening...
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.
my sql statement is a little more complex. i'll look it over more closely and let you know.
rwv,
can you show us your SQL and table structures? It will easy to help you.
-----
Igor.
can you show us your SQL and table structures? It will easy to help you.
-----
Igor.
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('dtlas tplay').As DateTime := tn - gMinReplayTime/24;
qTracks.Open;
except
ok := false;
end;
TrackQuery := ok;
end;
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('dtlas
qTracks.Open;
except
ok := false;
end;
TrackQuery := ok;
end;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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') .AsDateTim e
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
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')
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
ASKER
correction
' set lastplay = :paramNow'
ParamByName('paramNow').As DateTime := now;
' set lastplay = :paramNow'
ParamByName('paramNow').As
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
begin
query1.Close;
query1.ParamByName('param'
query1.Open;
end;
do you get an errormessage?
meikl