2 Replies Latest reply on May 16, 2016 8:49 AM by d4nm

    SWQL GetDate()

    d4nm

      Hi all,

       

      I cannot get the GetDate() function to work in a query.  I have the code for a brilliant SQL job query written by another user.  It lists all jobs and their status.

       

      I want to list all jobs other than succeeded and this works fine but I also want to only list the ones for the last 48 hours.  You can see near the bottom I have added the GetDate but it refuses to work.

       

      Can one of you experts tell me what I am doing wrong?

        • Re: SWQL GetDate()
          xtraspecialj

          Try this instead:

           

          i.LastRunStatus <> 1 AND i.LastRunDate > ADDDAY(-2, GETDATE())
          

           

          SWQL doesn't know what you want subtracted when you just say GETDATE() - 2.  Two what?  Days, hours, minutes, milliseconds?  It has no way of knowing.  In SQL you would do something like DATEADD(d, -2, GETDATE()) but in SWQL they have specialized functions for the different date parts.  Download the "SWQL Examples.txt" file from this post here and you'll see all of them and more.

           

          Be careful with datetimes in SWQL as well.  A lot of datetimes are in UTC, even when the SQL table will have it in the SQL Server's local time.  If LastRunDate is in UTC then you either need to convert it to Local by wrapping it in TOLOCAL(i.LastRunDate) or use GETUTCDATE() instead.  Either way works depending on what you want it to evaluate against.  I almost always convert my timestamp fields to Local so that when people choose a date time they get the result they want and don't have to convert it themselves.

           

          Also, you can put code directly into your Thwack post like I did above my clicking "Use Advanced Editor" in the top-righthand corner of the text box, click the two little >> symbols in the editor's toolbar, choose "Syntax Highlighting" and click "SQL".  Now you can paste in your code and it will look like mine above.

           

          Good luck!

          5 of 5 people found this helpful