convert or dateadd - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

convert or dateadd

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-28 : 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)

7020 Posts

Posted - 2010-09-28 : 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

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-28 : 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)

7020 Posts

Posted - 2010-09-28 : 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

217 Posts

Posted - 2010-09-28 : 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)

7020 Posts

Posted - 2010-09-28 : 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

217 Posts

Posted - 2010-09-28 : 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.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-28 : 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

217 Posts

Posted - 2010-10-04 : 10:53:08

I've resolved this issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources