none
PowerBI - SQL Queries for Dates RRS feed

  • Question

  • Hi guys,

    First time poster! 

    We've just connected ServiceNow to PowerBI via an ODBC. 

    As the database in SNOW is quite immense, I was hoping to query the last x months of data to minimise the strain on the system. Does anyone know the correct language to use here? I have tried the below (which works when I leave out the date operator). But once I add the "sys_created_on" field it stops functioning. 

    Query as below: 

    SELECT number, category, subcategory, dv_assignment_group, sys_created_on, opened_at, 
    closed_at, u_resolved, dv_state, sys_class_name, priority, dv_location, active, sys_updated_on,
    dv_assigned_to, dv_u_resolved_by
     FROM task
       WHERE sys_created_on = now() - 90
          ORDER BY sys_created_on ;

    Thanks in advance! 

    Mike  

    Tuesday, May 7, 2019 4:45 AM

All replies

  • WHERE sys_created_on = now() - 90

    Hello Mike,

    You perform an equal compare and the VBA (?) function Returns date with timestamp, so you would get only data from exact the same timestamp 90 days before. Use higher-equal compare instead

    WHERE sys_created_on >= now() - 90


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 7, 2019 5:59 AM
    Moderator
  • Hi Olaf,

    Sorry that was a typo on my behalf. I have tried using the >= which strangely doesn't return an error but instead gives me an empty table. 

    The timestamps in ServiceNow are shown in a date/time format. Would that change anything? 

    Thanks in advance,

    Mike 

    Tuesday, May 7, 2019 6:16 AM
  • Hi Olaf,

    Sorry that was a typo on my behalf. I have tried using the >= which strangely doesn't return an error but instead gives me an empty table. 

    The timestamps in ServiceNow are shown in a date/time format. Would that change anything? 

    Thanks in advance,

    Mike 

    I've heard SNOW uses MySQL

    If that's the case you can use a query like below to get 90 days data

    SELECT number, category, subcategory, dv_assignment_group, sys_created_on, opened_at, 
     closed_at, u_resolved, dv_state, sys_class_name, priority, dv_location, active, sys_updated_on,
     dv_assigned_to, dv_u_resolved_by
      FROM task
        WHERE sys_created_on >= DATE_SUB(curdate(),INTERVAL 90 DAY)
          ORDER BY sys_created_on ;


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, May 7, 2019 7:48 AM
  • Hey there,

    Thanks for replying. Unfortunately the string didn't work either. I have tried DATEADD as well but had no luck. Doubt this will help, but the error with the above solution is as below:

    Syntax error in SQL statement. syntax error line 3 at or after token <90>.[10179]

    So I'm assuming it may have something to do with the 90 string? 

    Thanks again.

    Mike 

    Wednesday, May 8, 2019 2:15 AM