June 25, 2012 at 11:56 pm
hi, i want to show time difference between two datetime fields like here
IF OBJECT_ID('TempDB..#mytable1','U') IS NOT NULL
DROP TABLE #mytable1
CREATE TABLE #mytable1
(
ID INT IDENTITY(1,1) ,
UName NVARCHAR(50),
alert_datetime DATETIME
)
SET IDENTITY_INSERT #mytable1 ON
INSERT INTO #mytable1
(ID, UName,alert_datetime )
SELECT '1','ABC','2011-07-06 14:12:00' UNION ALL
SELECT '2','XYZ','2011-07-06 14:12:10'
SET IDENTITY_INSERT #mytable1 OFF
SELECT * from #mytable1
Now i want to show Duration:10 minutes.
Note:i want to have same date time pattern as i provided as "2011-07-06 14:12:10"
June 26, 2012 at 2:06 am
I'm sorry, but I don't understand what you want. You can get the difference between 2 datetime values by using the datediff function. This will return a number because you can't describe the difference between datetime with another datetime value. Also in your example the difference between the 2 values is 10 seconds and not 10 minute as you wrote.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 26, 2012 at 8:27 pm
Perhaps something like this?
SELECT ID, UName, alert_datetime
,Diff=CONVERT(VARCHAR(15), CAST(DATEADD(millisecond, (
SELECT DATEDIFF(millisecond, t2.alert_datetime, t1.alert_datetime)
FROM #mytable1 t2
WHERE t1.ID = t2.ID + 1), '2000-01-01') AS DATETIME) , 114)
FROM #mytable1 t1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 26, 2012 at 8:59 pm
The late Ken Henderson discusses this problem at length in one of the first chapters of his The Guru's Guide to Transact-SQL. That book is pre-2005 and many of his solutions, such as pivoting data without the PIVOT clause, are obsolete.
Nonetheless, many of his algorithms are still valid, and this is one of them.
I am sorry but I do not have this book at hand. If you cannot get hold of it right away, I could get hold of it tomorrow (Wednesday, my time).
I just don't have the time to recreate the code from memory.
June 27, 2012 at 6:48 am
Methew (6/25/2012)
hi, i want to show time difference between two datetime fields like hereIF OBJECT_ID('TempDB..#mytable1','U') IS NOT NULL
DROP TABLE #mytable1
CREATE TABLE #mytable1
(
ID INT IDENTITY(1,1) ,
UName NVARCHAR(50),
alert_datetime DATETIME
)
SET IDENTITY_INSERT #mytable1 ON
INSERT INTO #mytable1
(ID, UName,alert_datetime )
SELECT '1','ABC','2011-07-06 14:12:00' UNION ALL
SELECT '2','XYZ','2011-07-06 14:12:10'
SET IDENTITY_INSERT #mytable1 OFF
SELECT * from #mytable1
Now i want to show Duration:10 minutes.
Note:i want to have same date time pattern as i provided as "2011-07-06 14:12:10"
Hi Mathew,
As Adi said your requirement is not clear as how do you want to display the duration. However you can use datediff function for getting the difference.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 27, 2012 at 7:10 am
like this?
you'd have to work out what the criteria for joining the table is though
declare @table table(ID int, UName varchar(10),alert_datetime datetime)
iNSERT INTO @table
SELECT '1','ABC','2011-07-06 14:12:00' UNION ALL
SELECT '2','XYZ','2011-07-06 14:12:10'
select
t1alert,t2alert,
DATEDIFF(SECOND,t1alert,t2alert) as myDifference
from (
select
t1.alert_datetime as t1alert,
t2.alert_datetime as t2alert
from
@table t1
inner join @table t2 on t1.id + 1 = t2.id
)y
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply