SWQL Query DATETIME function help

chrkov73

I am trying to covert a date to the datetime format to do some date functions on it and I just cant get it to work.

I am trying to get 'Test Schedule' in the datetime format doing the following:

SELECT TOP 1 [Nodes].DisplayName
        , DATETIME((SELECT TOP 1 CurrentValue
            FROM Orion.NPM.CustomPollerAssignmentOnNode AS cPon
            WHERE cPon.CustomPollerName = 'upsAdvTestLastDiagnosticsDate' AND [Nodes].NodeID = cpon.NodeID
        )) AS [Test Schedule]
FROM Orion.Nodes AS [Nodes]
JOIN Cortex.Orion.PowerControlUnit AE ON AE.RelatedNode = [Nodes].NodeID
WHERE [Nodes].Vendor = 'American Power Conversion Corp.'
ORDER BY [Nodes].DisplayName ASC

But I keep getting an error of 'DATETIME" is not a recognized buit-in function name.

But yet it is as the following works.

SELECT TOP 1 [Nodes].DisplayName
        , (SELECT TOP 1 CurrentValue
            FROM Orion.NPM.CustomPollerAssignmentOnNode AS cPon
            WHERE cPon.CustomPollerName = 'upsAdvTestLastDiagnosticsDate' AND [Nodes].NodeID = cpon.NodeID
        ) AS [Test Schedule]
        , DATETIME ('07/23/2024') as T2
FROM Orion.Nodes AS [Nodes]
JOIN Cortex.Orion.PowerControlUnit AE ON AE.RelatedNode = [Nodes].NodeID
WHERE [Nodes].Vendor = 'American Power Conversion Corp.'
ORDER BY [Nodes].DisplayName ASC

and it returns a table with Test Schedule being 07/23/2024 and T2 comes back with 2024-07-23 00:00:00 

What am I missing?   I was expecting in the first example that Test Schedule would return 2024-07-23 00:00:00 

Thanks for any help.

adam.beedell

https://github.com/solarwinds/OrionSDK/wiki/SWQL-Functions

Available SWQL datetime functions here, the actual DATETIME one isnt well documented but we can add to it

Is the polled data in non-US date format? You get an error passing in too many months/half the non-US-format dates