 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Crazy Horse's crazier lit
Guest
|
 Posted: Thu Apr 07, 2005 9:45 pm Post subject: SQL conundrum revisited |
 |
|
Delphi 7, Firebird 1.5, FIB+ components.
I am trying to allow the user to select both a date range AND a time range
within each date.
IOW, they might select 4/1/2005 to 4/7/2005, but just want to look at "swing
shift".
This sql (in Data Base Work Bench):
select e.fullname, d.deptname, p.dayofweek, p.pchin, p.pchinloc, p.pchout,
p.pchoutloc
from pchup p
join empinfo e on e.empno = p.empno
join dept d on d.deptno = p.pchdept
where (id > 305)
and cast(p.PCHIN as date) between '2005/4/1' and '2005/4/7'
and cast(p.PCHIN as time) between '12:00:00 am' and '11:59:00 pm'
--gives the err msg "conversion error from string "11:59:00 pm"".
If '11:59:00 pm' is wrong, why is '12:00:00 am' accepted?
==================
In my app (Delphi 7), the "same" SQL:
select e.fullname, d.deptname, p.dayofweek, p.pchin, p.pchinloc, p.pchout,
p.pchoutloc from pchup
p join empinfo e on e.empno = p.empno join dept d on d.deptno = p.pchdept
where (id > 305) and
cast (p.PCHIN as date) between :FromDate and :ToDate
and cast (p.PCHIN as time) between :FromTime and :ToTime
with these params (FIB+ component):
FIBDataset.ParamByName('FromDate').AsDate := Trunc(dtpDateFrom.Date);
FIBDataset.ParamByName('ToDate').AsDate := Trunc(dtpDateTo.Date);
FIBDataset.ParamByName('FromTime').AsTime := Frac(dtpTimeFrom.Date);
FIBDataset.ParamByName('ToDate').AsTime := Frac(dtpTimeTo.Date);
gives err msg "Incompatible column/host variable data type. SQL error
code -303. Conversion error from string 1899-12-30" and then ""Incompatible
column/host variable data type. SQL error code -303. Conversion error from
string 23:59:00.0000"
Why am I getting these explosions, and more importantly still, what do I
need to get the results I so strongly desire?
--
Download Blackbird Crow Raven's book
"STILL CASTING SHADOWS: Two American Families 1620-2006"
(.exe and Delphi source): http://cc.borland.com/ccweb.exe/listing?id=23106
--email me for the most up-to-date manuscripts
|
|
| Back to top |
|
 |
Wayne Niddery [TeamB]
Guest
|
| Posted: Fri Apr 08, 2005 2:50 am Post subject: Re: SQL conundrum revisited |
|
|
Crazy Horse's crazier little brother wrote:
| Quote: | Delphi 7, Firebird 1.5, FIB+ components.
I am trying to allow the user to select both a date range AND a time
range within each date.
IOW, they might select 4/1/2005 to 4/7/2005, but just want to look at
"swing shift".
This sql (in Data Base Work Bench):
and cast(p.PCHIN as date) between '2005/4/1' and '2005/4/7'
and cast(p.PCHIN as time) between '12:00:00 am' and '11:59:00 pm'
|
Assuming PCHIN is a TimeStamp type, then you should not need CAST. If you
*must* use date strings, I would advise using MM/DD/YYY format. IB/FB accept
other formats/delimiters, but you're definitely safe with this. Parameters
are better though.
and p.PCHIN between '4/1/'2005' and '4/7/2005'
and EXTRACT(HOUR from p.PCHIN) Between 12 and 24
| Quote: | FIBDataset.ParamByName('FromDate').AsDate := Trunc(dtpDateFrom.Date);
FIBDataset.ParamByName('ToDate').AsDate := Trunc(dtpDateTo.Date);
FIBDataset.ParamByName('FromTime').AsTime := Frac(dtpTimeFrom.Date);
FIBDataset.ParamByName('ToDate').AsTime := Frac(dtpTimeTo.Date);
|
I'm not sure why, but I've seen trouble before with AsDate and AsTime, I
always just use AsDateTime. If my suggestion above works to get the data,
then change the AsTime parameters to AsInteger and assign the hour from the
datepickers.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"The moment the idea is admitted into society that property is not as
sacred as the laws of God and there is not a force of law and public
justice to protect it, anarchy and tyranny commence." - John Adams
|
|
| Back to top |
|
|
Lysander
Guest
|
| Posted: Fri Apr 08, 2005 7:10 am Post subject: Re: SQL conundrum revisited |
|
|
In article <4255a9e4$1 (AT) newsgroups (DOT) borland.com>, [email]cshannon (AT) d4sw (DOT) com[/email]
says...
| Quote: | I am trying to allow the user to select both a date range AND a time
range
within each date.
IOW, they might select 4/1/2005 to 4/7/2005, but just want to look at "swing
shift".
|
So sad to have an SQL-Server and to not use the server-side
processing... :)
Look at the code at the bottom; I used it to create a table for testing,
and the next part shows the code for a STORED PROC for FireBird 1.5.
I do not know your FB-Background so I specially was chosing clear
identifiers, please excuse the long identifier-names. If you change
identifiers to your own table, I expect that this will smoothly solve
your request.
--
ciao,
André
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
European dBase-conference dBKonEurope on June 3rd and 4th
English spoken conference around different database-topics
::
Info here: www.dbkon.org
~~~
/*********************************************************************/
/*** DDL for table 'test_datetimeselections' ***/
/*********************************************************************/
SET SQL DIALECT 3;
SET NAMES ISO8859_1;
CREATE TABLE TEST_DATETIMESELECTIONS (
ID_ENTRY CHAR(4) NOT NULL,
ID_PERSONNEL CHAR(3),
DT_ACTSTAMP TIMESTAMP
);
ALTER TABLE TEST_DATETIMESELECTIONS ADD CONSTRAINT PI_TESTDATETIMES
PRIMARY KEY (ID_ENTRY);
/* Privileges for the procedure! */
GRANT SELECT ON TEST_DATETIMESELECTIONS TO PROCEDURE
TEST_SP_DTSELECTION;
/*********************************************************************/
/*** DDL for procedure 'test_SP_DTSelection' ***/
/*********************************************************************/
CREATE PROCEDURE TEST_SP_DTSELECTION (
/* several Input-parameters */
IP_DSTARTDATE DATE,
IP_DENDDATE DATE,
IP_TSTARTTIME TIME,
IP_TENDTIME TIME)
RETURNS (
/* several output-params */
OP_IDENTRY CHAR(4),
OP_IDPERSONNEL CHAR(3),
OP_DTACTSTAMP TIMESTAMP)
AS
DECLARE VARIABLE V_IDENTRY CHAR(4);
DECLARE VARIABLE V_IDPERSONNEL CHAR(4);
DECLARE VARIABLE V_DTACTSTAMP TIMESTAMP;
begin
for
select
test_datetimeselections.id_entry,
test_datetimeselections.id_personnel,
test_datetimeselections.dt_actstamp
from
test_datetimeselections
where
cast(test_datetimeselections.dt_actstamp as date)
between :ip_dstartdate and :ip_denddate
and
cast(test_datetimeselections.dt_actstamp as time)
between :IP_TStartTime and :IP_TEndTime
into
:V_IDEntry,
:V_IDPersonnel,
:V_DTActstamp
do
begin
OP_IDEntry = :V_IDEntry;
OP_IDPersonnel = :V_IDPersonnel;
OP_DTActStamp = :V_DTActStamp;
suspend;
end
end
|
|
| Back to top |
|
|
Crazy Horse's crazier lit
Guest
|
| Posted: Fri Apr 08, 2005 6:38 pm Post subject: Re: SQL conundrum revisited |
|
|
"Crazy Horse's crazier little brother" <If you take a man fishing, he'll
understand why some people find golf exciting> wrote
| Quote: |
I am trying to allow the user to select both a date range AND a time range
within each date.
IOW, they might select 4/1/2005 to 4/7/2005, but just want to look at
"swing shift".
|
Thanks to an idea from Helen Borrie's "The Firebird Book," this works in
DBWB:
select e.fullname, d.deptname, p.dayofweek, p.pchin,
p.pchinloc, p.pchout, p.pchoutloc
from pchup p
join empinfo e on e.empno = p.empno
join dept d on d.deptno = p.pchdept
where (id > 305)
and cast (p.PCHIN as date) between '2005/4/5' and '2005/4/7'
and cast (extract (HOUR from p.pchin) as char(2))|| ':' ||
cast (extract (MINUTE from p.pchin) as char(2))|| ':' ||
cast (extract (SECOND from p.pchin) as char(7))
between '13:00:00.0000' and '23:59:59.9999'
....but I still need to get it to work in Delphi code, hopefully using
Params. If need be, Format() will probably work ok.
--
Download Blackbird Crow Raven's book
"STILL CASTING SHADOWS: Two American Families 1620-2006"
(.exe and Delphi source): http://cc.borland.com/ccweb.exe/listing?id=23106
--email me for the most up-to-date manuscripts
|
|
| Back to top |
|
|
Crazy Horse's crazier lit
Guest
|
| Posted: Fri Apr 08, 2005 9:15 pm Post subject: Re: SQL conundrum revisited |
|
|
"Crazy Horse's crazier little brother" <cshannon (AT) d4sw (DOT) com> wrote
| Quote: |
...but I still need to get it to work in Delphi code, hopefully using
Params. If need be, Format() will probably work ok.
|
Well, after much flailing about, I was able to get it to work as I wanted.
Oddly, I could not get the Time values as Parameters, but had to use
Format():
sCoreSQL = 'select e.fullname, d.deptname, p.dayofweek, p.pchin, '+
'p.pchinloc, p.pchout, p.pchoutloc '+
'from pchup p '+
'join empinfo e on e.empno = p.empno '+
'join dept d on d.deptno = p.pchdept '+
'where (id > 305) '+
'and cast (p.PCHIN as date) between :FromDate and :ToDate '+
'and cast (p.PCHIN as time) between %s and %s '+
'order by p.pchin';
.. . .
sTimeFrom := FormatTimeAsSQLTime(dtpTimeFrom.DateTime);
sTimeTo := FormatTimeAsSQLTime(dtpTimeTo.DateTime);
FIBDataset.SQLs.SelectSQL.Add(Format(sCoreSQL, [
QuotedStr(sTimeFrom), QuotedStr(sTimeTo)]));
{ Date/Time range }
FIBDataset.ParamByName('FromDate').AsDate := dtpDateFrom.Date;
FIBDataset.ParamByName('ToDate').AsDate := dtpDateTo.Date;
{------------------------------------------------------------------------------}
function FormatTimeAsSQLTime(ADateTime: TDateTime): String;
var
wHours, wMinutes, wSeconds, wMilliseconds: Word;
begin
DecodeTime(ADateTime, wHours, wMinutes, wSeconds, wMilliseconds);
Result := Format('%.2d:%.2d:%.2d.0000', [wHours, wMinutes, wSeconds]);
end;
--
Download Blackbird Crow Raven's book
"STILL CASTING SHADOWS: Two American Families 1620-2006"
(.exe and Delphi source): http://cc.borland.com/ccweb.exe/listing?id=23106
--email me for the most up-to-date manuscripts
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
