1 Reply Latest reply on Jul 20, 2015 3:08 PM by tdanner

    Help convert SQL to SWQL


      How do I convert this SQL to SWQL:


      select Status, RowID, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), DATEADD (second, convert(bigint,Status), '01/01/1970 00:00:00')) AS 'Local Time'

      from custompollerstatus

      where CustomPollerAssignmentID = 'XXXXXXXXXXXXXXXXXXXX'

      order by convert(int,RowID)

        • Re: Help convert SQL to SWQL

          To convert a unix timestamp to a local time in SWQL, you can use this:


              ToLocal(AddSecond(1437422294, DateTime('1970-01-01 00:00:00')))


          To convert a string to a number so you can use it with AddSecond or ORDER BY, just add 0.


          So the complete query would look like this:


          SELECT Status, RowID, ToLocal(AddSecond(Status+0, DateTime('1970-01-01 00:00:00'))) AS [Local Time]
          FROM Orion.NPM.CustomPollerStatus
          WHERE CustomPollerAssignmentID = 'f4952f3d-4a16-46b5-a5b0-220eac269cc4'
          ORDER BY RowID+0