Date/Time Math - 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

General SQL Server Forums

Database Design and Application Architecture

Date/Time Math

Author

Topic

JVisconti
Starting Member

47 Posts

Posted - 2009-09-17 : 13:41:42

I am looking for the best way to do Date/Time math.

Would it be a Excel macro/function? or a Stored procedure or function in SQL?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-17 : 13:45:30

Depends on where the data is now and what "math" you want to preform. Sql has a lot of datetime functions.

Be One with the Optimizer
TG

JVisconti
Starting Member

47 Posts

Posted - 2009-09-17 : 14:03:01

The data right now is stored in SQL and is being viewed in Excel. As far as the "math" I want to do is something like "Response Time" based on when a "ticket" was first opened, and when a "First Response" was entered into the system.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-17 : 14:09:44

sounds like a simple time difference between 2 datetime values so sql server's DATEDIFF function may be all you need. Have you looked through the link?

Be One with the Optimizer
TG

JVisconti
Starting Member

47 Posts

Posted - 2009-09-17 : 14:27:35

I just finished going through the link and I think that answered some of the questions I have.

JVisconti
Starting Member

47 Posts

Posted - 2009-09-17 : 14:49:44

One other thing, I seem to be having a problem wrapping my brain around the DADTEDIFF function. I see how it works, but I'm trying to tailor it to my specific case. i have a column in the Query called HistoryFirstResponse and a column called TicketOpenDateTime. I want the column in the query called RespTime to reflect the time difference between when the ticket was opened and when the First Response was entered. I just need syntax help.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-17 : 15:47:45

You don't really need DATEDIFF for this. This code will return the ResponseTime as an offset from 1900-01-01 00:00:00, which is the standard way to store an elapsed time in SQL Server.


select
	ResponseTime = HistoryFirstResponse-TicketOpenDateTime,
	TicketOpenDateTime,
	HistoryFirstResponse
from
	( -- Test Data
	select	TicketOpenDateTime	= convert(datetime,'20090917 07:13:24.347'),
		HistoryFirstResponse	= convert(datetime,'20090918 14:11:17.883')
	) a

Results:

ResponseTime             TicketOpenDateTime       HistoryFirstResponse
-----------------------  -----------------------  -----------------------
1900-01-02 06:57:53.537  2009-09-17 07:13:24.347  2009-09-18 14:11:17.883

If you need to parse the ResponseTime into other units of time, you can then use DATEADD or DATEPART.

CODO ERGO SUM

JVisconti
Starting Member

47 Posts

Posted - 2009-09-17 : 16:11:06

Now say I want to add the ResponseTime column to an existing table, I ran the code as you gave it, and it shows up in a separate table. The table that has all the raw data is the same table I want the ResponseTime column to show also.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 21:48:00

[code]
select *, ResponseTime = datediff(minute, TicketOpenDateTime, HistoryFirstResponse)
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources