[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL DateTime Comparison

Posted on 2000-04-06
11
Medium Priority
?
223 Views
Last Modified: 2010-04-04
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.
0
Comment
Question by:rwv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2692301
? 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
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 2692311
Hi rwv,

What error do you get ?

Regards,
The Mayor
0
 
LVL 2

Expert Comment

by:Felixin
ID: 2692370
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 2692375
felixin
n/24 is ok
meikl
0
 
LVL 9

Expert Comment

by:ITugay
ID: 2692436
listening...
0
 

Author Comment

by:rwv
ID: 2692470
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
 
LVL 9

Expert Comment

by:ITugay
ID: 2692495
rwv,
can you show us your SQL and table structures? It will easy to help you.
-----
Igor.
0
 

Author Comment

by:rwv
ID: 2692593
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
 
LVL 9

Accepted Solution

by:
ITugay earned 600 total points
ID: 2692625
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
 

Author Comment

by:rwv
ID: 2693683
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
 

Author Comment

by:rwv
ID: 2693691
correction
' set lastplay = :paramNow'
ParamByName('paramNow').AsDateTime := now;


 
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question