SQL Query Translation - DateTrunc

[Anonymous]

Hello,

Can someone please help me get this query adjusted for SQL statement? It works in a web-based report in Solar Winds but not in SQL.

DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())

The full query is:

SELECT
       Interfaces.InterfaceId,
       Nodes.NodeID,
       Nodes.Caption AS NodeName,
       Interfaces.Caption AS Interface_Caption,
       Interfaces.CustomProperties.Important_Interfaces AS Important_Ints,
       Maxbps_In,
       Maxbps_Out,
       Maxbps_In95,
       Maxbps_Out95,
       Maxbps_In99,
       Maxbps_Out99
     FROM Orion.Nodes
     INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID     INNER JOIN (
       SELECT
         OuterInterfaceTraffic.InterfaceID,
         (SELECT MAX(InAverageBps) as maxInAverageBps FROM (
           SELECT TOP 100 PERCENT InAverageBps
           FROM Orion.NPM.InterfaceTraffic
           INNER JOIN Orion.NPM.InterfacesCustomProperties AS ONICP ON InterfaceTraffic.InterfaceID = ONICP.InterfaceID
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND ONICP.Important_Interfaces = 'Field_WAN' AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY InAveragebps ASC )
           ) AS Maxbps_In,         (SELECT MAX(OutAverageBps) as maxOutAverageBps FROM (
           SELECT TOP 100 PERCENT OutAveragebps
           FROM Orion.NPM.InterfaceTraffic
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY OutAveragebps ASC )
           ) AS Maxbps_Out,
         (SELECT MAX(InAveragebps) as maxInMaxbps FROM (
           SELECT TOP 95 PERCENT InAveragebps
           FROM Orion.NPM.InterfaceTraffic
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY InMaxbps ASC )
           ) AS Maxbps_In95,
         (SELECT MAX(OutAveragebps) as maxOutMaxbps FROM (
           SELECT TOP 95 PERCENT OutAveragebps
           FROM Orion.NPM.InterfaceTraffic
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY OutMaxbps ASC )
           ) AS Maxbps_Out95,
         (SELECT MAX(InAverageBps) as maxInAverageBps FROM (
           SELECT TOP 99 PERCENT InAveragebps
           FROM Orion.NPM.InterfaceTraffic
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY InAveragebps ASC )
           ) AS Maxbps_In99,         (SELECT MAX(OutAverageBps) as maxOutAverageBps FROM (
           SELECT TOP 99 PERCENT OutAveragebps
           FROM Orion.NPM.InterfaceTraffic
           INNER JOIN Orion.NPM.InterfacesCustomProperties AS ONICP ON InterfaceTraffic.InterfaceID = ONICP.InterfaceID
           WHERE
             InterfaceID = OuterInterfaceTraffic.InterfaceID AND ONICP.Important_Interfaces = 'Field_WAN' AND
             DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
             ORDER BY OutAveragebps ASC )
             ) AS Maxbps_Out99           FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic
           INNER JOIN Orion.NPM.InterfacesCustomProperties AS ONICP ON OuterInterfaceTraffic.InterfaceID = ONICP.InterfaceID
           WHERE OuterInterfaceTraffic.DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
           AND ONICP.Important_Interfaces = 'Field_WAN'
           GROUP BY OuterInterfaceTraffic.InterfaceID
         ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID

Any help is greatly appreciated! Thanks!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If you want to convert the following code to SQL code,

DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())

You could try the following SQL in SQL Server

BETWEEN 
DATEADD(MONTH,DATEDIFF(month, 0,GETUTCDATE() )-1,0) 
AND 
DATEADD(month, DATEDIFF(month, 0,GETUTCDATE() ), 0)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

PERFECT! Thanks!