SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 convert or dateadd
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/28/2010 :  12:10:58  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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
7020 Posts

Posted - 09/28/2010 :  12:21:37  Show Profile  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/28/2010 :  12:38:56  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
This is for a payroll program and the seconds represent how long an agent was logged in during their shift.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/28/2010 :  13:48:33  Show Profile  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/28/2010 :  14:04:38  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/28/2010 :  14:27:40  Show Profile  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/28/2010 :  15:01:02  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/28/2010 :  15:07:14  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 10/04/2010 :  10:53:08  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
I've resolved this issue.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000