girlswants_me
asked on
Compute Date and Time with SQL
How will i get the difference between the two Date/time fields using sql?
fields are:
BadgeNo, DateIn, TimeIn, DateOut, TimeOut
example (Inside the Database)
"EMPLOYEE.DB"
badgeNo fullName
01 Mark oNe
02 Edwin Two
03 Sofia Three
"DTR.DB"
badgeNo DateIn TimeIn DateOut TimeOut
01 11/21/2003 08:00:00 am 11/21/2003 09:00:00 am
02 11/21/2003 08:00:00 am 11/21/2003 10:00:00 am
01 11/21/2003 08:00:00 am 11/21/2003 11:00:00 am
03 11/21/2003 08:00:00 am 11/21/2003 09:00:00 am
WHEN displayed in my Grid
badgeNo FullName Total_Time
01 Mark oNe 4
02 Edwin Two 2
03 Sofia Three 1
Note:
formula must be (DateOut+timeOut) - (DateIn + TimeIn)
example
( 11/21/2003+09:00:00 am ) - (11/21/2003 + 08:00:00 am)
answer is
1 hr.
Anybody can help?
fields are:
BadgeNo, DateIn, TimeIn, DateOut, TimeOut
example (Inside the Database)
"EMPLOYEE.DB"
badgeNo fullName
01 Mark oNe
02 Edwin Two
03 Sofia Three
"DTR.DB"
badgeNo DateIn TimeIn DateOut TimeOut
01 11/21/2003 08:00:00 am 11/21/2003 09:00:00 am
02 11/21/2003 08:00:00 am 11/21/2003 10:00:00 am
01 11/21/2003 08:00:00 am 11/21/2003 11:00:00 am
03 11/21/2003 08:00:00 am 11/21/2003 09:00:00 am
WHEN displayed in my Grid
badgeNo FullName Total_Time
01 Mark oNe 4
02 Edwin Two 2
03 Sofia Three 1
Note:
formula must be (DateOut+timeOut) - (DateIn + TimeIn)
example
( 11/21/2003+09:00:00 am ) - (11/21/2003 + 08:00:00 am)
answer is
1 hr.
Anybody can help?
what for types are these fields (DateIn, TimeIn, DateOut, TimeOut)?
Date, time, datetime, char, varchar?
what database?
Date, time, datetime, char, varchar?
what database?
Depends on the database server that you're using. This is not a Delphi issue but a database issue. And not all databases will allow calculations with date fields.
The database is Paradox I think ("EMPLOYEE.DB") and BDE is being used perhaps.
Now the question that remains is: what are the types of the fields.
Now the question that remains is: what are the types of the fields.
Assuming your DateX fields are of DATE type and your TimeX fields are of TIME type then your SQL may look like (Paradox):
SELECT B.badgeNo, A.fullName,
SUM(CAST((
(B.DateOut - B.DateIn) * 24
+
(B.TimeOut - B.TimeIn) * 24
) AS INTEGER))
AS B.Total_Time FROM "EMPLOYEE.DB" AS A, "DTR.db" AS B
WHERE A.badgeNo = B.badgeNo
GROUP BY B.badgeNo, A.fullName
ORDER BY B.badgeNo
Regards, Geo
SELECT B.badgeNo, A.fullName,
SUM(CAST((
(B.DateOut - B.DateIn) * 24
+
(B.TimeOut - B.TimeIn) * 24
) AS INTEGER))
AS B.Total_Time FROM "EMPLOYEE.DB" AS A, "DTR.db" AS B
WHERE A.badgeNo = B.badgeNo
GROUP BY B.badgeNo, A.fullName
ORDER BY B.badgeNo
Regards, Geo
ASKER
Hi everyone,
I used this to create the database
procedure TDataModule1.CreateTable_O TDTR(NameF ile: string; databasePath:string);
var
DatabaseDirectory:string;
begin
// Create Database
with TTable.Create(Application) do
begin
Active := False;
DatabaseName := databasePath;
TableName := NameFile;
TableType := ttDBase;
with FieldDefs do
begin
Clear;
Add('IndexNo' , ftString , 15 , false);
Add('BadgeNo' , ftString , 6 , false);
Add('ShiftCode' , ftString , 5 , false);
Add('MealRate' , ftFloat , 0 , false);
Add('DateIn' , ftDate , 0 , false);
Add('TimeIn' , ftTime , 0 , false);
Add('DateOut' , ftDate , 0 , false);
Add('TimeOut' , ftTime , 0 , false);
Add('SchedCode' , ftString , 4 , false);
end;
CreateTable;
with IndexDefs do
begin
Clear;
Add('IndexNo' , 'IndexNo' , []);
end;
CreateTable;
Free;
end;
end;
I used this to create the database
procedure TDataModule1.CreateTable_O
var
DatabaseDirectory:string;
begin
// Create Database
with TTable.Create(Application)
begin
Active := False;
DatabaseName := databasePath;
TableName := NameFile;
TableType := ttDBase;
with FieldDefs do
begin
Clear;
Add('IndexNo' , ftString , 15 , false);
Add('BadgeNo' , ftString , 6 , false);
Add('ShiftCode' , ftString , 5 , false);
Add('MealRate' , ftFloat , 0 , false);
Add('DateIn' , ftDate , 0 , false);
Add('TimeIn' , ftTime , 0 , false);
Add('DateOut' , ftDate , 0 , false);
Add('TimeOut' , ftTime , 0 , false);
Add('SchedCode' , ftString , 4 , false);
end;
CreateTable;
with IndexDefs do
begin
Clear;
Add('IndexNo' , 'IndexNo' , []);
end;
CreateTable;
Free;
end;
end;
ASKER
This cause a Type mismatch error
wwQuery1.SQL.Add('SELECT B.BadgeNo, A.EmpName,SUM((((B.DateOut - B.DateIn) * 24)+((B.TimeOut - B.TimeIn) * 24))) AS B.Total_Time FROM '+main.DB_Employee+' AS A, '+main.DB_OTDTR+' AS B');
wwQuery1.SQL.Add('WHERE A.BadgeNo = B.BadgeNo');
wwQuery1.SQL.Add('GROUP BY B.BadgeNo, A.EmpName, B.DateOut');
wwQuery1.SQL.Add('ORDER BY B.BadgeNo');
wwQuery1.ExecSQL;
wwQuery1.Open;
Browse.DataSource:=wwDataS ource2;
wwQuery1.SQL.Add('SELECT B.BadgeNo, A.EmpName,SUM((((B.DateOut
wwQuery1.SQL.Add('WHERE A.BadgeNo = B.BadgeNo');
wwQuery1.SQL.Add('GROUP BY B.BadgeNo, A.EmpName, B.DateOut');
wwQuery1.SQL.Add('ORDER BY B.BadgeNo');
wwQuery1.ExecSQL;
wwQuery1.Open;
Browse.DataSource:=wwDataS
ASKER
My database is not a paradox.
Please, follow the exact SQL statement I've provided:
- I used CAST AS INTEGER for the SUM and you didn't.
- Also B.DateOut cannot be in the GROUP BY because you don't have such a column in your SELECT list.
- Remove wwQuery1.ExecSQL; - this is wrong !
- Browse.DataSource:=wwDataS ource2; should be before wwQuery1.Open; if this is something related to the query.
Regards, Geo
- I used CAST AS INTEGER for the SUM and you didn't.
- Also B.DateOut cannot be in the GROUP BY because you don't have such a column in your SELECT list.
- Remove wwQuery1.ExecSQL; - this is wrong !
- Browse.DataSource:=wwDataS
Regards, Geo
ASKER
The first source code you've posted was first applied but it failed on that "CAST" part.
That is why i removed that area.
That is why i removed that area.
ASKER
wwQuery1.DatabaseName:=mai n.Database _Path;
wwQuery1.SQL.Clear;
wwQuery1.SQL.Add('SELECT B.BadgeNo, A.EmpName,');
wwQuery1.SQL.Add('SUM(CAST ((');
wwQuery1.SQL.Add('(B.DateO ut - B.DateIn) * 24');
wwQuery1.SQL.Add('+');
wwQuery1.SQL.Add('(B.TimeO ut - B.TimeIn) * 24');
wwQuery1.SQL.Add(') AS INTEGER))');
wwQuery1.SQL.Add(' AS B.Total_Time FROM "'+main.DB_Employee+'" AS A, "'+main.DB_OTDTR+'" AS B');
wwQuery1.SQL.Add('WHERE A.BadgeNo = B.BadgeNo');
wwQuery1.SQL.Add('GROUP BY B.BadgeNo, A.EmpName');
wwQuery1.SQL.Add('ORDER BY B.BadgeNo');
wwQuery1.Open;
Browse.DataSource:=wwDataS ource2;
STILL HAVING PROBLEM.
"Type Mismatch in expression." <--- error
wwQuery1.SQL.Clear;
wwQuery1.SQL.Add('SELECT B.BadgeNo, A.EmpName,');
wwQuery1.SQL.Add('SUM(CAST
wwQuery1.SQL.Add('(B.DateO
wwQuery1.SQL.Add('+');
wwQuery1.SQL.Add('(B.TimeO
wwQuery1.SQL.Add(') AS INTEGER))');
wwQuery1.SQL.Add(' AS B.Total_Time FROM "'+main.DB_Employee+'" AS A, "'+main.DB_OTDTR+'" AS B');
wwQuery1.SQL.Add('WHERE A.BadgeNo = B.BadgeNo');
wwQuery1.SQL.Add('GROUP BY B.BadgeNo, A.EmpName');
wwQuery1.SQL.Add('ORDER BY B.BadgeNo');
wwQuery1.Open;
Browse.DataSource:=wwDataS
STILL HAVING PROBLEM.
"Type Mismatch in expression." <--- error
Your database is not Paradox. OK. It's DBASE then, I guess. Is that right? Please, tell me what it is (not what it isn't). I'm confused because you're naming your tables .DB (paradox) but setting TableType to ttDBase later on.
If your db is DBASE then ftTime fields are CHAR(11) actually not TIME (as they would be in Paradox). That's why you can't substract them directly. You should use:
(CAST(B.TimeOut AS TIME) - CAST(B.TimeIn AS TIME)) * 24
instead of
(B.TimeOut - B.TimeIn) * 24
That's all.
Regards, Geo
If your db is DBASE then ftTime fields are CHAR(11) actually not TIME (as they would be in Paradox). That's why you can't substract them directly. You should use:
(CAST(B.TimeOut AS TIME) - CAST(B.TimeIn AS TIME)) * 24
instead of
(B.TimeOut - B.TimeIn) * 24
That's all.
Regards, Geo
ASKER
wwQuery1.DatabaseName:=mai n.Database _Path;
wwQuery1.SQL.Clear;
wwQuery1.SQL.Add('SELECT B.BadgeNo, A.EmpName,');
wwQuery1.SQL.Add('SUM(CAST ((');
wwQuery1.SQL.Add('(CAST(B. DateOut AS TIME) - CAST(B.DateIn AS TIME)) * 24');
wwQuery1.SQL.Add('+');
wwQuery1.SQL.Add('(CAST(B. TimeOut AS TIME) - CAST(B.TimeIn AS TIME)) * 24');
wwQuery1.SQL.Add(') AS INTEGER))');
wwQuery1.SQL.Add(' AS B.Total_Time FROM "'+main.DB_Employee+'" AS A, "'+main.DB_OTDTR+'" AS B');
wwQuery1.SQL.Add('WHERE A.BadgeNo = B.BadgeNo');
wwQuery1.SQL.Add('GROUP BY B.BadgeNo, A.EmpName');
wwQuery1.SQL.Add('ORDER BY B.BadgeNo');
wwQuery1.Open;
I already used the "CAST" but
STILL HAVING PROBLEM.
"Type Mismatch in expression." <--- error
wwQuery1.SQL.Clear;
wwQuery1.SQL.Add('SELECT B.BadgeNo, A.EmpName,');
wwQuery1.SQL.Add('SUM(CAST
wwQuery1.SQL.Add('(CAST(B.
wwQuery1.SQL.Add('+');
wwQuery1.SQL.Add('(CAST(B.
wwQuery1.SQL.Add(') AS INTEGER))');
wwQuery1.SQL.Add(' AS B.Total_Time FROM "'+main.DB_Employee+'" AS A, "'+main.DB_OTDTR+'" AS B');
wwQuery1.SQL.Add('WHERE A.BadgeNo = B.BadgeNo');
wwQuery1.SQL.Add('GROUP BY B.BadgeNo, A.EmpName');
wwQuery1.SQL.Add('ORDER BY B.BadgeNo');
wwQuery1.Open;
I already used the "CAST" but
STILL HAVING PROBLEM.
"Type Mismatch in expression." <--- error
I'm sorry to hear that. Both versions (paradox and dbase) work just fine here.
ASKER
Here is the complete code that i used. Hope this will help, or maybe i have a problem with my query table used.
I used TQuery.
procedure TSubsidy.Button2Click(Send er: TObject);
begin
wwQuery1.DatabaseName:=mai n.Database _Path;
wwQuery1.SQL.Clear;
wwQuery1.SQL.Add('SELECT B.BadgeNo, A.EmpName,');
wwQuery1.SQL.Add('SUM(((') ;
wwQuery1.SQL.Add('((B.Date Out) - (B.DateIn)) * 24');
wwQuery1.SQL.Add('+');
wwQuery1.SQL.Add('((B.Time Out) - (B.TimeIn)) * 24');
wwQuery1.SQL.Add(')))');
wwQuery1.SQL.Add('AS B.Total_Time FROM "'+main.DB_Employee+'" AS A, "'+main.DB_OTDTR+'" AS B');
wwQuery1.SQL.Add('WHERE A.BadgeNo = B.BadgeNo');
wwQuery1.SQL.Add('GROUP BY B.BadgeNo, A.EmpName');
wwQuery1.SQL.Add('ORDER BY B.BadgeNo');
wwQuery1.Open;
Browse.DataSource:=wwDataS ource2;
end;
in my datamodule
procedure TDataModule1.CreateTable_O TDTR(NameF ile: string; databasePath:string);
var
DatabaseDirectory:string;
begin
// Create Database
with TTable.Create(Application) do
begin
Active := False;
DatabaseName := databasePath;
TableName := NameFile;
TableType := ttDBase;
with FieldDefs do
begin
Clear;
Add('IndexNo' , ftString , 15 , false);
Add('BadgeNo' , ftString , 6 , false);
Add('ShiftCode' , ftString , 5 , false);
Add('MealRate' , ftFloat , 0 , false);
Add('DateIn' , ftDate , 0 , false);
Add('TimeIn' , ftFloat , 0 , false);
Add('DateOut' , ftDate , 0 , false);
Add('TimeOut' , ftFloat , 0 , false);
Add('SchedCode' , ftString , 4 , false);
end;
CreateTable;
with IndexDefs do
begin
Clear;
Add('IndexNo' , 'IndexNo' , []);
end;
CreateTable;
Free;
end;
end;
I used TQuery.
procedure TSubsidy.Button2Click(Send
begin
wwQuery1.DatabaseName:=mai
wwQuery1.SQL.Clear;
wwQuery1.SQL.Add('SELECT B.BadgeNo, A.EmpName,');
wwQuery1.SQL.Add('SUM(((')
wwQuery1.SQL.Add('((B.Date
wwQuery1.SQL.Add('+');
wwQuery1.SQL.Add('((B.Time
wwQuery1.SQL.Add(')))');
wwQuery1.SQL.Add('AS B.Total_Time FROM "'+main.DB_Employee+'" AS A, "'+main.DB_OTDTR+'" AS B');
wwQuery1.SQL.Add('WHERE A.BadgeNo = B.BadgeNo');
wwQuery1.SQL.Add('GROUP BY B.BadgeNo, A.EmpName');
wwQuery1.SQL.Add('ORDER BY B.BadgeNo');
wwQuery1.Open;
Browse.DataSource:=wwDataS
end;
in my datamodule
procedure TDataModule1.CreateTable_O
var
DatabaseDirectory:string;
begin
// Create Database
with TTable.Create(Application)
begin
Active := False;
DatabaseName := databasePath;
TableName := NameFile;
TableType := ttDBase;
with FieldDefs do
begin
Clear;
Add('IndexNo' , ftString , 15 , false);
Add('BadgeNo' , ftString , 6 , false);
Add('ShiftCode' , ftString , 5 , false);
Add('MealRate' , ftFloat , 0 , false);
Add('DateIn' , ftDate , 0 , false);
Add('TimeIn' , ftFloat , 0 , false);
Add('DateOut' , ftDate , 0 , false);
Add('TimeOut' , ftFloat , 0 , false);
Add('SchedCode' , ftString , 4 , false);
end;
CreateTable;
with IndexDefs do
begin
Clear;
Add('IndexNo' , 'IndexNo' , []);
end;
CreateTable;
Free;
end;
end;
Wow, time fields have become of float type now! Let it be...
Try changing:
wwQuery1.SQL.Add('((B.Time Out) - (B.TimeIn)) * 24');
to
wwQuery1.SQL.Add('CAST(((B .TimeOut) - (B.TimeIn)) * 24) AS INTEGER');
Regards, Geo
Try changing:
wwQuery1.SQL.Add('((B.Time
to
wwQuery1.SQL.Add('CAST(((B
Regards, Geo
ASKER
i have received "Invalid use of token ")""
Please see this code. I wonder whats wrong with this.
wwQuery1.DatabaseName:=mai n.Database _Path;
wwQuery1.SQL.Clear;
wwQuery1.SQL.Add('SELECT B.BadgeNo, A.EmpName,');
wwQuery1.SQL.Add('SUM(((') ;
wwQuery1.SQL.Add('((B.Time Out) - (B.TimeIn)) * 24');
wwQuery1.SQL.Add('+');
wwQuery1.SQL.Add('CAST(((B .TimeOut) - (B.TimeIn)) * 24) AS INTEGER');
wwQuery1.SQL.Add(')))');
wwQuery1.SQL.Add('AS B.Total_Time FROM "'+main.DB_Employee+'" AS A, "'+main.DB_OTDTR+'" AS B');
wwQuery1.SQL.Add('WHERE A.BadgeNo = B.BadgeNo');
wwQuery1.SQL.Add('GROUP BY B.BadgeNo, A.EmpName');
wwQuery1.SQL.Add('ORDER BY B.BadgeNo');
wwQuery1.Open;
Browse.DataSource:=wwDataS ource2;
Please see this code. I wonder whats wrong with this.
wwQuery1.DatabaseName:=mai
wwQuery1.SQL.Clear;
wwQuery1.SQL.Add('SELECT B.BadgeNo, A.EmpName,');
wwQuery1.SQL.Add('SUM(((')
wwQuery1.SQL.Add('((B.Time
wwQuery1.SQL.Add('+');
wwQuery1.SQL.Add('CAST(((B
wwQuery1.SQL.Add(')))');
wwQuery1.SQL.Add('AS B.Total_Time FROM "'+main.DB_Employee+'" AS A, "'+main.DB_OTDTR+'" AS B');
wwQuery1.SQL.Add('WHERE A.BadgeNo = B.BadgeNo');
wwQuery1.SQL.Add('GROUP BY B.BadgeNo, A.EmpName');
wwQuery1.SQL.Add('ORDER BY B.BadgeNo');
wwQuery1.Open;
Browse.DataSource:=wwDataS
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much geobul, For you price, i've raised the points to 200.
Youre the Master of Delphi
Youre the Master of Delphi
Thank you very much :-)
BTW the third line in my last comment above is wrong. It should be:
wwQuery1.SQL.Add('(B.DateO ut - B.DateIn) * 24');
Regards, Geo
BTW the third line in my last comment above is wrong. It should be:
wwQuery1.SQL.Add('(B.DateO
Regards, Geo
ASKER