Under DBISAM I used the below code to extract data between two date-time stamp fields. This does not work under ElevateDB. I am wondering how I can make it work. I am not too familiar with casting and have had trouble trying other methods.  I did double check my regional setting for the following format.  yyyy-mm-dd hh:mm:ss AM . I noticed with that the precision time-stamp has four millisecond digits where there may have to be further adjustment, and which I would also like to use.



//used when adding calendar date picker with DBISAM date-time stamp fields with seconds and AM or PM.

Edit1.text := DatetoStr(DateTimePicker9.date)+ ' ' + TimetoStr(DateTimePicker1.time);
Edit2.text := DatetoStr(DateTimePicker10.date)+ ' ' + TimetoStr(DateTimePicker2.time);


// SQL between my DBISAM date-time stamps - works nice
DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE >' + QuotedStr(Edit1.Text) + 'and orderdate <' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ;


Any help is appreciated to get the above working with ElevateDB.

John Postnikoff.

John

>>DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE >' + QuotedStr(Edit1.Text) + >>'and orderdate <' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ;


Dates are no longer referenced as strings in EDB. A bit of a bind if you are used to free-and-easier DBISAM ... but a good thing once you understand concepts like INTERVALs ... which bring a lot of power.


Your code will be much closer to working if you used the following in place of your existing code:

' SELECT * From Shippers WHERE ORDERDATE > CAST(' + QuotedStr(Edit1.Text) + ' AS TIMESTAMP)

Note that I have simply wrapped your previous String injection with:

CAST('+YourString+' as Timestamp)

I don't wish to be an old grandmother ... but I would suggest 2 other possible improvements:

Use FORMAT('SELECT* From Shippers WHERE ORDERDATE > CAST('%s' AS TIMESTAMP) ', [QuotedStr(Edit1.Text)])

... using the %s just makes things easier to read.

I would also suggest considering using Params in place of String fangling, i.e.

MyQuery.SQL:= 'SELECT* From Shippers WHERE ORDERDATE > :DateParam'
MyQuery.Prepare;
MyQuery.ParamByName('DateParam').asDateTime:= StrToDate(Edit1.Text);

I know this is longer, but: You now test whether the SQL will prepare in code ... saving time with badly written SQL, and you pass in your params in a type-safe way which leaves it to EDB to convert the data into the correct data-format.

I hope this is useful & not too hectoring.

Adam

John


I agree with Adam about using a parameterised query being the best approach.

If you don't go down that route I wouldn't use CAST but simple preface the correctly formatted and quoted timestamp string with the keyword TIMESTAMP eg

DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE > TIMESTAMP ' + QuotedStr(Edit1.Text) + 'and orderdate < TIMESTAMP ' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ;

INTERVALs are very powerful but can take a bit of getting your head round.


Roy Lambert [Team Elevate]

Thanks guys for the feedback. I know have a much to learn in this area, along with using parameters. As always I very much appreciate the great support, which is the reason I am here. Sometimes its hard to train an old dog like me new tricks.

John

Adam Brett wrote:

John

>>DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE >' + QuotedStr(Edit1.Text) + >>'and orderdate <' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ;


Dates are no longer referenced as strings in EDB. A bit of a bind if you are used to free-and-easier DBISAM ... but a good thing once you understand concepts like INTERVALs ... which bring a lot of power.


Your code will be much closer to working if you used the following in place of your existing code:

' SELECT * From Shippers WHERE ORDERDATE > CAST(' + QuotedStr(Edit1.Text) + ' AS TIMESTAMP)

Note that I have simply wrapped your previous String injection with:

CAST('+YourString+' as Timestamp)

I don't wish to be an old grandmother ... but I would suggest 2 other possible improvements:

Use FORMAT('SELECT* From Shippers WHERE ORDERDATE > CAST('%s' AS TIMESTAMP) ', [QuotedStr(Edit1.Text)])

... using the %s just makes things easier to read.

I would also suggest considering using Params in place of String fangling, i.e.

MyQuery.SQL:= 'SELECT* From Shippers WHERE ORDERDATE > :DateParam'
MyQuery.Prepare;
MyQuery.ParamByName('DateParam').asDateTime:= StrToDate(Edit1.Text);

I know this is longer, but: You now test whether the SQL will prepare in code ... saving time with badly written SQL, and you pass in your params in a type-safe way which leaves it to EDB to convert the data into the correct data-format.

I hope this is useful & not too hectoring.

Adam

I am wondering if you can look at this again. It does not seem to work.  If I try to the other method using CAST I get a Elevate DB 1011 conversion error. I have not tried to use parameters just yet and was hoping to get what you suggested below working.  I am trying to get a better handle on intervals and am wondering if you can direct me to some reference that I can study in detail.

Roy Lambert wrote:

John


I agree with Adam about using a parameterised query being the best approach.

If you don't go down that route I wouldn't use CAST but simple preface the correctly formatted and quoted timestamp string with the keyword TIMESTAMP eg

DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE > TIMESTAMP ' + QuotedStr(Edit1.Text) + 'and orderdate < TIMESTAMP ' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ;

INTERVALs are very powerful but can take a bit of getting your head round.


Roy Lambert [Team Elevate]

John


EDBManager is your friend. I just tested

SELECT
_HideAlarm
FROM
Calls
WHERE
_HideAlarm > TIMESTAMP '2011-01-01 12:00'
AND
_HideAlarm < TIMESTAMP '2011-10-01 12:00'


and its fine.

What's the contents of your edits? It could be wrong which would also explain a CAST error. Also what exactly do you mean by "It does not seem to work"?

Roy Lambert [Team Elevate]

Roy,

I never had any problems using the EDB Manager and used the time stamps as I have below.  There must be something in the string that is not converting, I even tried to add a space and use other formats. I change my date separator from / to - and did have to make sure the date range was a valid date without zeros in the months etc.  Here is my code for review again. This should be very simple. I do not know what I am missing that is not showing results. I am to a point that I do no longer get any errors, just no results on the table. To review here is what I have. Must be simple that I cannot see.


//used when adding calendar date and time from pickers
Edit1.text := DatetoStr(DateTimePicker9.date)+ ' ' + TimetoStr(DateTimePicker11.time);
Edit2.text := DatetoStr(DateTimePicker10.date)+ ' ' + TimetoStr(DateTimePicker12.time);

// contents of edits.
// result for edit box 1   2000-01-01 12:00:01 AM
// result for edit box 2   2011-12-31 11:59:59 PM


// case
 1 : DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE > TIMESTAMP' + QuotedStr(Edit1.Text) + 'and ORDERDATE < TIMESTAMP'+ QuotedStr(Edit2.Text) + 'and CUSTCODE =' + QuotedStr(Edit3.Text));



John Postnikoff






Roy Lambert wrote:

John


EDBManager is your friend. I just tested

SELECT
_HideAlarm
FROM
Calls
WHERE
_HideAlarm > TIMESTAMP '2011-01-01 12:00'
AND
_HideAlarm < TIMESTAMP '2011-10-01 12:00'


and its fine.

What's the contents of your edits? It could be wrong which would also explain a CAST error. Also what exactly do you mean by "It does not seem to work"?

Roy Lambert [Team Elevate]

I have solved the problem.  To make a long story short.  The culprit was the use of the date separator in Windows regional settings.  When I realized it was still using yyyy/mm/dd  instead of yyyy-mm-dd on my development machine and compile application all worked as it was supposed to.  

John


John Postnikoff wrote:


Under DBISAM I used the below code to extract data between two date-time stamp fields. This does not work under ElevateDB. I am wondering how I can make it work. I am not too familiar with casting and have had trouble trying other methods.  I did double check my regional setting for the following format.  yyyy-mm-dd hh:mm:ss AM . I noticed with that the precision time-stamp has four millisecond digits where there may have to be further adjustment, and which I would also like to use.



//used when adding calendar date picker with DBISAM date-time stamp fields with seconds and AM or PM.

Edit1.text := DatetoStr(DateTimePicker9.date)+ ' ' + TimetoStr(DateTimePicker1.time);
Edit2.text := DatetoStr(DateTimePicker10.date)+ ' ' + TimetoStr(DateTimePicker2.time);


// SQL between my DBISAM date-time stamps - works nice
DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE >' + QuotedStr(Edit1.Text) + 'and orderdate <' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ;


Any help is appreciated to get the above working with ElevateDB.

John Postnikoff.

Adam,

Just a minor nit:

<< Dates are no longer referenced as strings in EDB. >>

This isn't exactly true.  You still reference them like strings, but you
need to preface them with the appropriate DATE, TIME, or TIMESTAMP keyword:

DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE > DATE '
+ Engine.QuotedSQLStr(Edit1.Text) + 'and orderdate <  DATE ' +
Engine.QuotedSQLStr(Edit2.Text) + 'and custnumber =' +
Engine.QuotedSQLStr(Edit3.Text)) ;

That should do the trick.

--
Tim Young
Elevate Software
www.elevatesoft.com

Duly noted Tim, thanks. Never really clocked using the "TIMESTAMP" keyword before.