| Author |
 Topic  |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
 Posted - 09/28/2010 : 12:10:58
|
I have the following query:
SELECT [Employees].name, [Employees].Employeenumber,
Convert ([Timestamp]-([LoggedIn]/1000)/60))/1440+1,"mm/dd/yy"as [DATE],
FROM Employees INNER JOIN mOpInterval ON [Employees].Loginname1 = mOpInterval.Opname
and I'm trying to convert seconds to valid dates. Is convert or dateadd the best to work with here and can someone give me an example of how the syntax of this query runs?
Thank you,
Doug |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 09/28/2010 : 12:21:37
|
What do the "seconds" represent? An offset from a particular date?
Examples of conversion for two different start points.
select
-- With offset starting from 1900-01-01 00:00:00
MyDate1 = dateadd(dd,MySeconds/86400,0)+dateadd(ss,MySeconds%86400,0),
-- With offset starting from 1970-01-01 00:00:00
MyDate2 = dateadd(dd,MySeconds/86400,'19700101')+dateadd(ss,MySeconds%86400,'19700101')
from
( --Test Data
select Myseconds = 3568960000
) a
Results:
MyDate1 MyDate2
----------------------- ------------------------
2013-02-04 09:46:40.000 2153-02-04 09:46:40.000
(1 row(s) affected)
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 09/28/2010 12:27:43 |
 |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 09/28/2010 : 12:38:56
|
| This is for a payroll program and the seconds represent how long an agent was logged in during their shift. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 09/28/2010 : 13:48:33
|
quote:
Originally posted by dougancil
This is for a payroll program and the seconds represent how long an agent was logged in during their shift.
If the seconds is a duration, how can that be converted to a date, since that is not what it represents?
CODO ERGO SUM |
|
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 09/28/2010 : 14:04:38
|
Michael,
Sorry let me be more precise. They do represent a date, and time that a user was logged in but it's calculated in seconds, which is why you see this calculation (1000)/60))/1440+1,) thrown in behind that. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 09/28/2010 : 14:27:40
|
I don't understand what you are after.
It would help if you posted scripts for creating the tables involved, scripts to insert sample data into those tables, and a sample of the output you expect it to produce.
CODO ERGO SUM |
|
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 09/28/2010 : 15:01:02
|
Micheal,
I didnt create the tables involved so I can't offer you that. This is what was written for me in Access and I'm trying to get the same information from a SQL query:
SELECT [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy") AS [Date], Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd") AS [Day], Sum(([LoggedIn]/1000/60)) AS LogIn, Sum(([OnTime]/1000/60)) AS OnTime1, Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd") AS Da INTO 1_1ScratchPad
FROM 1_1Employee INNER JOIN dbo_mOpLogout ON [1_1Employee].Employee_Login = dbo_mOpLogout.Opname
GROUP BY [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy"), Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd"), Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")
HAVING (((Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")) Between [Start (YY/MM/DD)] And [End (YY/MM/DD)]));
What that query should produce for me is the sum of hours for all employees with a "start yy/mm/dd" and an "end yy/mm/dd"
Last_First_Name Employee_Number Date Day LogIn OnTime1 Da
agent, some 9999 06/06/10 Sunday 481 454 10/06/06
Thats an example of the data produced by the query in Access.
|
Edited by - dougancil on 09/28/2010 15:07:50 |
|
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 09/28/2010 : 15:07:14
|
| Micheal ... one other thing ... that access query produces a result like that for each day that the user is logged in for. Then there is a summation that is done at the end of the week. |
|
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 10/04/2010 : 10:53:08
|
| I've resolved this issue. |
|
|
| |
Topic |
|
convert or dateadd
