String to date conversion in SWQL: DATETIME is not a recognized built-in function name

[mammuthus]

Orion 2020.2.6, SWQL Studio 2.6.0.238

I have some dataset like this:	CurrentValue	Caption
03/15/2021	a.host
04/25/2021	b.host
Unknown	c.host
09/06/2021	d.host

CurrentValue contains a date in MM/DD/YYYY format (actually a string value). I want to compare it with the current date minus 180 days.

🌆 Screenshots and queries


⬆️ QUERY

```sql SELECT [UDCP].CurrentValue AS [Date as Stored] , [UDCP].Node.Caption AS [Node Caption] , DATETIME([UDCP].CurrentValue) AS [Date as Date] FROM Orion.NPM.CustomPollerAssignment AS [UDCP] WHERE [UDCP].CurrentValue != 'Unknown' ```


⬆️ QUERY

```sql SELECT DATETIME ([ChildQuery].CurrentValue) AS [LastBatteryCalibrationDate] , [ChildQuery].NodeCaption FROM ( SELECT [UDCP].CurrentValue , [UDCP].Node.Caption AS [NodeCaption] FROM Orion.NPM.CustomPollerAssignment AS [UDCP] WHERE [UDCP].CurrentValue != 'Unknown' AND [UDCP].CustomPollerName = 'Battery_last_calibration' AND [UDCP].CustomPollerOid = '1.3.6.1.4.1.318.1.1.1.7.2.7.0' AND [UDCP].Node.Vendor = 'American Power Conversion Corp.' ) AS [ChildQuery] ```


⬆️ QUERY

```sql SELECT [UDCP].CurrentValue AS [CurrentValue_Original] , [UDCP].Node.Caption AS [NodeCaption] , DateTime([UDCP].CurrentValue) AS [CurrentValue_Date] FROM Orion.NPM.CustomPollerAssignment AS [UDCP] WHERE [UDCP].CurrentValue != 'Unknown' AND [UDCP].CustomPollerName = 'Battery_last_calibration' AND [UDCP].CustomPollerOid = '1.3.6.1.4.1.318.1.1.1.7.2.7.0' AND [UDCP].Node.Vendor = 'American Power Conversion Corp.' ```

With @kmsigma we have tried several queries but with the same result: DATETIME is not a recognized built-in function name error. I'm not sure this is an expected behavior because DATETIME time function is described as SWQL function here.

[mammuthus]

Still actual for 3.1.0.343 :(

[msandecki-holcim]

Over 1 year later and the issue is still there and no new version of SWQL Studio has been released. It's surprising that Solarwinds started using SWQL internally in the product and at the same time the tool to build queries does not work correctly and there are no quick fixes to long lasting issues.

[msandecki-holcim]

I tried to find when DATETIME works and when it doesn't. The example queries are:

This works: SELECT DATETIME('08:30') AS value FROM Orion.Nodes n

This doesn't work ('DATETIME' is not a recognized built-in function name.): SELECT DATETIME(SUBSTRING('08:30', 1, 5)) AS value FROM Orion.Nodes n

This works: SELECT DATETIME(SUBSTRING('08:30', 1, 5)) AS value FROM System.Diagnostic

Tested on Orion Platform HF5, NCM HF2, NPM HF3, NTA HF2, UDT HF3: 2020.2.6, using SWQL Studio 3.1.0.343. Hope this helps to move it forward.

[chrkov]

Any update on this. I am running into this same issue.