This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL DATETIME HELP

FormerMember over 10 years ago

I'm hoping the SQL gurus can spot what the the problem is with the following SQL statement. It works but the time is off by 1 hour. For example, if the node is down for 15 minutes, the down time is reported as 1 hour 15 min. Does anyone see what the problem is and what is needed to fix it? I'm not very SQL savvy and got the datetime conversion from another post. What am I missing?

SELECT StatusLED

, NodeID

, Caption

, Status

, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc) AS LastSystemUpTimePoll

,convert(varchar(10), (DATEDIFF(d,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc),GETDATE()))) + ' Days ' +

convert(varchar(10), (DATEDIFF(hh,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc),GETDATE())% 24 )) + ' Hours '+convert(varchar(10), (DATEDIFF(mi,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc),GETDATE()) % 60)) + ' Mins ' as 'DD:HH:MM:SS'

From Nodes

WHERE StatusLED = 'down.gif'

ORDER BY 6 desc

0 Aforsythe over 10 years ago

Wow that looks like some of the substring stuff I do, one of my co-workers says I'm going to wear out the parenthesis key.
The problem is that Datediff will round. You'll want to just adjust your math there to use the seconds datepart in your DateDiff statements instead of days, hours and minutes and then you should be ok.
Cancel
Vote Up 0 Vote Down

Cancel
0 FormerMember over 10 years ago in reply to Aforsythe

LOL! Thanks for your help Acy! How’s this? It looks like it is working but I don't have a node down under an hour right now.
, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc) AS LastSystemUpTimePoll
,convert(varchar(10), (DATEDIFF(d,DATEADD(ss, DATEDIFF(ss, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc),GETDATE()))) + ' Days ' +
convert(varchar(10), (DATEDIFF(hh,DATEADD(ss, DATEDIFF(ss, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc),GETDATE()) % 24 )) + ' Hours '+convert(varchar(10), (DATEDIFF(mi,DATEADD(ss, DATEDIFF(ss, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc),GETDATE()) % 60)) + ' Mins ' as 'DD:HH:MM:SS'
Cancel
Vote Up 0 Vote Down

Cancel
0 Aforsythe over 10 years ago in reply to FormerMember

Same issue...
It's the days and hours part.
(DATEDIFF(d,DATEADD ...

(DATEDIFF(hh,DATEADD ..

You'll have to do this:

Days = (Get Seconds / 86400)
Hours = ((Get Seconds - ((Get Seconds / 86400) * 86400) ) / 3600)
Minutes = (Get Seconds - (((Get Seconds / 86400) * 86400) + ( ((Get Seconds - ((Get Seconds / 86400) * 86400) ) / 3600) * 3600 )) / 60)

It helps me to write what I need like that. Then I can go through the Pseudo-code and replace "Get Seconds" with
DATEDIFF(ss,LastSystemUptimepollutc,getUTCdate()) That helps with the crossed eyes a bit.
Cancel
Vote Up 0 Vote Down

Cancel

0 Aforsythe over 10 years ago in reply to Aforsythe

And that get's us here: (PS I beleive I'm missing a set of parenthesis in my previous post for minutes, but it's fixed below:

SELECT StatusLED
, NodeID
, Caption
, Status
, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc) AS LastSystemUpTimePoll
, DATEDIFF(ss,LastSystemUptimepollutc,getUTCdate())
, 
Convert(Varchar(10),
(DATEDIFF(ss,LastSystemUptimepollutc,getUTCdate()) / 86400)
) 
+ ' Days ' +

Convert (Varchar(10),
((DATEDIFF(ss,LastSystemUptimepollutc,getUTCdate()) - ((DATEDIFF(ss,LastSystemUptimepollutc,getUTCdate()) / 86400) * 86400) ) / 3600)
)
+ ' Hours ' + 
Convert (Varchar(10),

((DATEDIFF(ss,LastSystemUptimepollutc,getUTCdate()) - (((DATEDIFF(ss,LastSystemUptimepollutc,getUTCdate()) / 86400) * 86400) + (((DATEDIFF(ss,LastSystemUptimepollutc,getUTCdate()) - ((DATEDIFF(ss,LastSystemUptimepollutc,getUTCdate()) / 86400) * 86400) ) / 3600) * 3600))) / 60)

) + ' Minutes '

FROM Nodes
WHERE StatusLED = 'down.gif'

ORDER BY 6 desc