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.
rwvAsked:
Who is Participating?
 
ITugayConnect With a Mentor Commented:
Still thinking...
but one crazy idea, is it possible you have different datetime format while making update by update query(dd/mm/yy and mm/dd/yy)?
----
Igor.
0
 
kretzschmarCommented:
? 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
0
 
wimmeyvaertCommented:
Hi rwv,

What error do you get ?

Regards,
The Mayor
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
FelixinCommented:
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
0
 
kretzschmarCommented:
felixin
n/24 is ok
meikl
0
 
ITugayCommented:
listening...
0
 
rwvAuthor Commented:
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.
0
 
ITugayCommented:
rwv,
can you show us your SQL and table structures? It will easy to help you.
-----
Igor.
0
 
rwvAuthor Commented:
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;
0
 
rwvAuthor Commented:
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
0
 
rwvAuthor Commented:
correction
' set lastplay = :paramNow'
ParamByName('paramNow').AsDateTime := now;


 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.