4 Replies Latest reply on Sep 30, 2013 5:19 PM by Aforsythe

    SQL DATETIME HELP

    rgward

      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

        • Re: SQL DATETIME HELP
          Aforsythe

          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.

            • Re: SQL DATETIME HELP
              rgward

              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'

                • Re: SQL DATETIME HELP
                  Aforsythe

                  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.

                   

                   

                   


                    • Re: SQL DATETIME HELP
                      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