How do you calculate Period Of Time

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

General SQL Server Forums

New to SQL Server Programming

How do you calculate Period Of Time

Author

Topic

Bren582
Starting Member

14 Posts

Posted - 2008-01-27 : 14:45:39

How do you build a query to calculate the time of employement or any space of time for each person in a table? I'm thinking I would want to use a "Current Date" and "Select Datediff" calculations of some sort and reference the hiredate against the current date but I have been unable to find much on this type of query.. Please help..

Thanks..

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-27 : 14:49:20

Do you have table schema and sample data?

Bren582
Starting Member

14 Posts

Posted - 2008-01-27 : 15:53:56

Here you go.. I have many table entries to process.. But this is a good example of the nature of the table. So I want to determine the length of time between the hire date and current date..

CREATE TABLE EMPLOYEE
(
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL,
Hiredate SmallDateTime NOT NULL
)

INSERT INTO EMPLOYEE VALUES
('Smith','Joe','12-DEC-2003');

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-27 : 16:19:40

This may help. You could use it directly or "steal" some code:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729

Be One with the Optimizer
TG

Bren582
Starting Member

14 Posts

Posted - 2008-01-27 : 20:34:53

Wow.. thats a whole lotta info.. I'm having trouble even finding what I should use..

Would this work? I'm a bit confused on the syntax here and how I would apply it.. How would I incorporate into my code?

DECLARE @dt DATETIME SET @dt = '20050325' SELECT DATEADD(d,1,@dt)

Thanks..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-27 : 20:47:25

lol. Yes, that would be a lot to take in for someone with not too much sql experience yet. Let's start over.
You want the difference in time between 2 datetime values, correct?

declare @dt1 datetime, @dt2 datetime
select @dt1 = getdate(), @dt2 = getdate()+1
select datediff(millisecond, @dt1, @dt2)

Check out Books Online for DATEDIFF

Other DateParts available for DATEDIFF

Year
quarter
Month
Day
Week
Hour
minute
second
millisecond

Be One with the Optimizer
TG

Bren582
Starting Member

14 Posts

Posted - 2008-01-28 : 10:24:05

Thats correct..
I need to incorporate the time period between hiredate and the current date into the following code which on its own will output the lastname and hiredate for each employee:

CREATE VIEW Service_Time
AS
SELECT LastName, Hiredate
FROM EMPLOYEE
Group By LastName, Hiredate
GO
SELECT * FROM [Service_Time]

How do I get this into the code above?

declare @dt1 datetime, @dt2 datetime
select @dt1 = getdate(), @dt2 = getdate()+1
select datediff(millisecond, @dt1, @dt2)

Thanks..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-28 : 10:29:55

like this:


SELECT	LastName
	,Hiredate 
	,datediff(millisecond, Hiredate, getdate()) as [MillisecondsOfEmployment]
FROM	EMPLOYEE
Group By LastName
	,Hiredate

Be One with the Optimizer
TG

Bren582
Starting Member

14 Posts

Posted - 2008-01-28 : 11:05:54

Hmmm,,

That is throwing the following error.. Any ideas ?

21 row(s) affected)
Msg 535, Level 16, State 0, Line 8
Difference of two datetime columns caused overflow at runtime.

Bren582
Starting Member

14 Posts

Posted - 2008-01-28 : 11:12:32

Found this as a reason for that error.. Question is how do you fix it or for simplicity sake I only need to output down to whole days as a time period.. Is that possible or resonable??

re:
DATEDIFF produces an error if the result is out of range for integer
values. For milliseconds, the maximum number is 24 days, 20 hours, 31
minutes and 23.647 seconds. For seconds, the maximum number is 68 years

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-28 : 11:14:56

I assume you really aren't interested in the number of Milliseconds a person has been employed.

I suggest changning the the <datepart> to a more appropriate value.

Be One with the Optimizer
TG

Bren582
Starting Member

14 Posts

Posted - 2008-01-28 : 11:34:52

Ahh yes.. Often times the answer is obvious yet completely escapes me..

I add..
Day
Month
Year etc..
Each report back OK.. Thanks TG !!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:39:37

Just a sidenote, beware that YEAR and MONTH may not calculate correctly.
Not as you might think it does...

See http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources