This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

String to date conversion in SWQL

omavel over 2 years ago

Is it possible to convert a string value to the date format with SWQL somehow?

I have some metrics from custom poller:

CurrentValue	Caption
03/15/2021	a.host
04/25/2021	b.host
11/13/2020	c.host
Unknown	d.host
09/06/2021	e.host

"CurrentValue" contains a date in MM/DD/YYYY format (actually a string value), and I want to compare it with the current date minus 180 days.
Comparison is not working with this query, and I think because "CurrentValue" type is not a "date":

SELECT UDCP.CurrentValue, UDCP.Node.Caption

FROM Orion.NPM.CustomPollerAssignment UDCP

WHERE Displayname = 'Battery_last_test'
    AND CurrentValue NOT LIKE 'Unknown'
    AND CurrentValue < ADDDAY( -180, GETDATE() )

It pops timeout error.

Found some info about DateTime function, but I can't make it work:

So I'd like to convert it to the date type before comparison. Is this possible?

Top Replies

KMSigma.SWI over 2 years ago in reply to omavel +1

We have to be Friends first. It's explained in the THWACK documentation [Creating Content] .

Parents

0 KMSigma.SWI over 2 years ago

Ah - I see what you are doing. Remove the quotes from inside the DateTime() Function.

SELECT DateTime(UDCP.CurrentValue)
Cancel
Vote Up 0 Vote Down

Cancel
0 omavel over 2 years ago in reply to KMSigma.SWI

Well, function is not recognized
Cancel
Vote Up 0 Vote Down

Cancel

0 KMSigma.SWI over 2 years ago in reply to omavel

Do you get the same error if you send over:

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'

omavel over 2 years ago in reply to KMSigma.SWI

This reply was deleted.
Cancel
Cancel
0 KMSigma.SWI over 2 years ago in reply to omavel

That picture above isn't SQWL Studio, it's the database manager. Try in SWQL Studio.
Cancel
Vote Up 0 Vote Down

Cancel
0 omavel over 2 years ago in reply to KMSigma.SWI

Oh sorry. Still the same:
Cancel
Vote Up 0 Vote Down

Cancel
0 KMSigma.SWI over 2 years ago in reply to omavel
Very odd. How about if you run:

SELECT TOP 1000 Name FROM Metadata.Functions WHERE Name LIKE '%Date%'

This will get the list of valid functions for the API.

Also, if I didn't ask, what Orion Platform version are you running?
Cancel
Vote Up 0 Vote Down

Cancel
0 omavel over 2 years ago in reply to KMSigma.SWI

KMSigma.SWI said:
Orion Platform version

2020.2.6
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 omavel over 2 years ago in reply to KMSigma.SWI

KMSigma.SWI said:
Orion Platform version

2020.2.6
Cancel
Vote Up 0 Vote Down

Cancel

Children

0 KMSigma.SWI over 2 years ago in reply to omavel

Well, that function is certainly available. Odd. I don't have any CustomPollers in my lab that return MM/DD/YYYY, so I don't have an easy way to test these out.

Last check - maybe the CurrentValue is some off format.

SELECT [UDCP].CurrentValue AS [DateAsStored]
     , [UDCP].Node.Caption AS [Node Caption]
     , DATETIME(ToString([UDCP].CurrentValue)) AS [DateAsDate]
FROM Orion.NPM.CustomPollerAssignment AS [UDCP]
WHERE [UDCP].CurrentValue != 'Unknown'

If that fails, then we need to make sure the rest is ok.

-- Commenting out the conversion
SELECT [UDCP].CurrentValue AS [DateAsStored]
     , [UDCP].Node.Caption AS [Node Caption]
--   , DATETIME(ToString([UDCP].CurrentValue)) AS [DateAsDate]
FROM Orion.NPM.CustomPollerAssignment AS [UDCP]
WHERE [UDCP].CurrentValue != 'Unknown'

If the first works, then great! If not, can you save the results of the second a CSV and attach them?

0 omavel over 2 years ago in reply to KMSigma.SWI

KMSigma.SWI said:
Last check - maybe the CurrentValue is some off format.

Same error here :<

KMSigma.SWI said:
If that fails, then we need to make sure the rest is ok.

How can I send the results to you in more private way?
Not sure if Thwack has a PM function Lol found it. Just a min.
Oh, you are not messagable :< Cannot Message
Cancel
Vote Up 0 Vote Down

Cancel
0 KMSigma.SWI over 2 years ago in reply to omavel

We have to be Friends first. It's explained in the THWACK documentation [Creating Content].
Cancel
Vote Up +1 Vote Down

Cancel