Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

DateTime Logic for SWQL

Trying to query for everything last month. This bit works great in SQL...what is the equivalent in SWQL?

DateTime BETWEEN DATEADD(MONTH,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

Find more posts tagged with

Accepted answers

KMSigma.SWI

I think this is the logic you'd want for the WHERE clause:

<span class="s5">WHERE</span><span class="s0"> </span><span class="s19">DateTime</span><span class="s0"> </span><span class="s5">BETWEEN</span><span class="s0"> </span><span class="s19">DATETRUNC</span><span class="s10">(</span><span class="s7">&#39;month&#39;</span><span class="s10">,</span><span class="s0"> </span><span class="s19">AddDate</span><span class="s10">(</span><span class="s7">&#39;month&#39;</span><span class="s10">,</span><span class="s0"> </span><span class="s4">-1</span><span class="s10">,</span><span class="s0"> </span><span class="s19">GETDATE</span><span class="s10">()))</span><span class="s0"> </span><span class="s5">AND</span><span class="s0"> </span><span class="s19">DATETRUNC</span><span class="s10">(</span><span class="s7">&#39;month&#39;</span><span class="s10">,</span><span class="s0"> </span><span class="s19">GETDATE</span><span class="s10">())</span><span class="s0">&nbsp;</span>

<span class="s0"></span>

Example that runs in clean in SWQL Studio:

SELECT i.InterfaceId
, i.OutPercentUtil
, i.Datetime
, AddDate('month', -1, GETDATE()) AS [OneMonthAgo]
, DATETRUNC('month', AddDate('month', -1, GETDATE())) AS [BeginDate]
, DATETRUNC('month', GETDATE()) AS [EndDate]
FROM Orion.NPM.InterfaceTraffic AS i
WHERE DateTime BETWEEN DATETRUNC('month', AddDate('month', -1, GETDATE())) AND DATETRUNC('month', GETDATE())
AND i.InterfaceId = 1119
ORDER BY DATETIME DESC

Obviously, my InterfaceID would need to be changed and you can remove the spurious 'date' fields that I was using to make sure the logic was sound.

All comments

KMSigma.SWI

Did you look at Date/Time Functions on the Orion SDK wiki?

nerengm

Yes, and frankly it just frustrates me that SWQL is so similar, yet different.

KMSigma.SWI

What are you specifically looking for? I'm seeing minutes and months above, but I'm not 100% what the WHERE clause should show. (Sorry - I'm only an accidental SQL person)

jm_sysadmin

He said 'Last month, so maybe:

MONTHDIFF(ToLocal(DateTime), Getdate()) = 1 -- Use for Monthly report

nerengm

SELECT i.INTERFACEID,i.OutPercentUtil,i.Datetime
FROM Orion.NPM.InterfaceTraffic i
--WHERE i.interfaceid = '10075' AND ((i.DateTime BETWEEN DATEADD(MONTH,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) <- SQL
WHERE i.interfaceid = '10075' AND (i.DateTime BETWEEN (adddate ('month',-1,GETDATE())) and GETDATE()) -- <- SWQL
ORDER BY DATETIME DESC