4. Date Filter

4. Date Filter

Custom Date Filter Format:

PGSQL

createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP ' 2019-01-01 00:00:00') * 1000 AS BIGINT)  AND
createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP ' 2019-01-30 00:00:00') * 1000 AS BIGINT)  

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician",
       ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
  AND wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-01-01 00:00:00') * 1000 AS BIGINT)
  AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-01-30 00:00:00') * 1000 AS BIGINT)
ORDER BY 5

MSSQL

dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ( createdtime/1000),'1970-01-01 00:00:00') >= convert(varchar,' 2019-01-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ( createdtime/1000),'1970-01-01 00:00:00') <= convert(varchar,' 2019-01-30 23:59',21)  

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician",
       ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
  AND dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= convert(varchar,'2019-01-01 00:00', 21)
  AND dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= convert(varchar,'2019-01-30 23:59', 21)
ORDER BY 5

Using Date Templates 

CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician",
       ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
  AND wo.CREATEDTIME >= <from_thisweek>
  AND wo.CREATEDTIME <= <to_thisweek>
ORDER BY 5

Available Date Templates

    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>
    • This year - <from_thisyear> - <to_thisyear>
    • Last year - <from_lastyear> - <to_lastyear>

PGSQL:

Last 7 days :

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) - '7 days'::interval,'yyyy-mm-dd 00:00:00'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS')

Last 15 days :

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) - '15 days'::interval,'yyyy-mm-dd 00:00:00'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS')

Last 30 days:

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) - '30 days'::interval,'yyyy-mm-dd 00:00:00'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS')

Next 7 days :

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) + '7 days'::interval,'yyyy-mm-dd 23:59:59'),'YYYY-MM-DD HH24:MI:SS')

Next 15 days :

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) + '15 days'::interval,'yyyy-mm-dd 23:59:59'),'YYYY-MM-DD HH24:MI:SS')

Next 30 days :

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) + '30 days'::interval,'yyyy-mm-dd 23:59:59'),'YYYY-MM-DD HH24:MI:SS')


MSSQL:

Last 7 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0) and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') <= GETDATE()

Last 15 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-15, 0) and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') <= GETDATE()

Last 30 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-30, 0) and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') <= GETDATE()

Next 7 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') >= GETDATE() and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= DATEADD(s,-1,DATEADD(day, DATEDIFF(day,0,GETDATE())+7,1))

Next 15 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') >= GETDATE() and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= DATEADD(s,-1,DATEADD(day, DATEDIFF(day,0,GETDATE())+15,1))

Next 30 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') >= GETDATE() and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= DATEADD(s,-1,DATEADD(day, DATEDIFF(day,0,GETDATE())+30,1))


AGING Days

PGSQL Syntax

extract(epoch from(now()::TIMESTAMP -  to_timestamp( wo.createdtime/1000)::TIMESTAMP))/3600/24

Sample query: 

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
  AND std.statusname = 'open'
  AND extract(epoch FROM(now()::TIMESTAMP - to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600/24 > 7

MSSQL  Syntax

DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +( wo.createdtime/1000),'1970-01-01 00:00:00'),GETDATE())

Sample query: 

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
  AND std.statusname = 'open'
  AND DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.createdtime/1000),'1970-01-01 00:00:00'),GETDATE()) > 7


Time Elapsed in HOURS

To convert in hours.

((wo.TIMESPENTONREQ))/1000/3600)  

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician",
       ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
ORDER BY 5


      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Report on Technician Leaves with date filter

                      DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 10000 SELECT cre.FIRST_NAME "Created By", LONGTODATE(TechUnavailability.CREATEDDATE) "Created Date", avail.FIRST_NAME "Technician on Leave", back.FIRST_NAME "Backup Technician", ...

                    • Query to show tickets first assigned today irrespective of created date

                      MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request Status", ...

                    • Query to find out the users added on a specific date range

                      This should be handy during Audit. Execute the query nder Reports->New Query Report Users added based on a date range. Here the range is from and to today. select * from aaauser where createdtime >= <from_today> AND CREATEDTIME <= <to_today> If ...

                    • How to disable the Created Date field for technicians

                      1. Roles Under Admin >> Roles >> Disable the below access The above option will disable the user from modifying Created Date, Resolved Date, Dueby Time. 2. Field and Form Rules If the requirement is to disable only Created Date field.  Under Admin >> ...

                    • Query report to get requestid,username ,email with created date ( MSSQL )

                      Tested in build MSSQL (14306) SELECT wo.WORKORDERID AS "Request ID", aaau.FIRST_NAME AS "Requester", aaaci.emailid,wo."TITLE" AS "Subject", "cd"."CATEGORYNAME" AS "Category", longtodate(wo.CREATEDTIME) AS "created time" FROM "WorkOrder" "wo" Left ...

                      Related Products