Datetime Anomaly with calculated column

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)

Datetime Anomaly with calculated column

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-27 : 09:22:24

Hi,

I want to produce an outcome table for test restores with the following columns:

Servername || DBName || Outcome || StartDate || RestoreDate || Duration.

Server1 || drew_test2 || Successful || 2007-07-27 14:06:00.197 || 2007-07-27 14:06:04.010 || 0 Hour(s) ,0 Minute(s), 7 Second(s)
Server1 || drew_test2 || Successful || 2007-07-27 14:06:11.007 || 2007-07-27 14:06:14.053 || 0 Hour(s) ,0 Minute(s), 7 Second(s)

The first 4 columns are populated first, then the duration is calulated like so:

Update TestRestoreOutcome
Set Duration = (
SELECT cast(floor(sum(datediff (ss, startDate, RestoreDate))/60/60) as char(2)) + 'Hour(s) ,' +
cast((floor(sum(datediff (ss, startDate, RestoreDate))/60)-floor(sum(datediff (ss, startDate, RestoreDate))/60/60)*60) as char(3)) + 'Minute(s), ' +
cast ((sum(datediff (ss, startDate, RestoreDate))-floor(sum(datediff (ss, startDate, RestoreDate))/60)*60) as char (3)) + 'Second(s)'
From testrestoreoutcome)

But what that is doing is adding the duration time from the first row to the next row i.e. if the first restore took 4 seconds, and the second restore took 3 seconds, the second row (representing the second restore) is showing as 7 seconds (4 + 3).

Can anyone explain why?

Thanks

Drew

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-07-27 : 10:43:52

Because you're using the SUM() function

I think you want:

Update TestRestoreOutcome
Set Duration = (
SELECT cast(floor(datediff (ss, startDate, RestoreDate)/60/60) as char(2)) + 'Hour(s) ,' +
cast((floor(datediff (ss, startDate, RestoreDate)/60)-floor(datediff (ss, startDate, RestoreDate)/60/60)*60) as char(3)) + 'Minute(s), ' +
cast ((datediff (ss, startDate, RestoreDate)-floor(datediff (ss, startDate, RestoreDate)/60)*60) as char (3)) + 'Second(s)'
From testrestoreoutcome)

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-27 : 10:53:15

The sneaky little thing. Thanks.

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 15:52:24

SELECT STUFF(STUFF(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108), 3, 1, ' hour(s), '), 15, 1, ' minute(s), ') + ' second(s)'

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 15:58:50

SELECT REPLACE(STUFF(STUFF(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108), 3, 1, ' hour(s), '), 15, 1, ' minute(s), ') + ' second(s)', '00', '0')

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

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-30 : 04:44:19

Thanks Peter.

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-07-30 : 23:36:22

Drew,

Displaying the duration as discreate hours, minutes, and seconds is something I believe you will learn to hate in the future. Why do you need this to be done... reporting?

--Jeff Moden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-31 : 03:54:08

Hi Jeff,

Yes. It's a table displaying a summary of test restore outcomes. One of the columns displays the time it took to for the db to restore. The column is a bit gimicky I suppose, but it could be useful for the very long restores. I don't think I'll be displaying many columns like that in the future :)

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-01 : 20:55:36

Do you have any restores in excess of 24 hours?

--Jeff Moden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-08-02 : 04:16:18

No... that would be a frikin long restore.

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-02 : 21:03:01

quote:
No... that would be a frikin long restore.

Drew

Heh... hoping that would be the answer... take a look at this... no need to even do the UPDATE to get the duration... it's "auto-magic"...

 CREATE TABLE #TestRestoreOutcome
        (
        Servername  SYSNAME,
        DBName      SYSNAME,
        Outcome     VARCHAR(30),
        StartDate   DATETIME,
        RestoreDate DATETIME,
        Duration AS CONVERT(CHAR(12),RestoreDate-StartDate,114) --Calculated Column
        )

 INSERT INTO #TestRestoreOutcome
        (Servername,DBName,Outcome,StartDate,RestoreDate) --Notice... not inserting "Duration"
 SELECT 'Server1','drew_test2','Successful','2007-07-27 14:06:00.197','2007-07-27 14:06:04.010' UNION ALL
 SELECT 'Server1','drew_test2','Successful','2007-07-27 14:06:11.007','2007-07-27 14:06:14.053'

 SELECT * FROM #TestRestoreOutcome

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources