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

    Help convert SQL to SWQL

    willie.snell@safetynational.com

      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
          tdanner

          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