How to convert UTC date to local time in an alert message

Question

What is the SQL syntax to convert a UTC date/time, such as in the datetime field for a unDP (universal device poller)? I keep getting:

MACRO SQL ERROR - Incorrect syntax near '7'.

for this statement:

Local time: ${SQL:Select DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), GETDATE()),('''' + ${CustomPollerStatus.DateTime} + ''''))}

unDP Taken (UTC time): 11/19/2013 7:45:30 PM

I realize it doesn't like the blank, and I need quote marks around the date so it treats it as an entire string, but I'm having trouble coming up with the right syntax.

marunderwood · Accepted Answer

After many trials and errors, I found the solution. There's no need to concatenate using the + and the ''''' to get one quote mark. SWQL is already parsing and concatenating, so you just need to insert the single quote mark:

Local time: ${SQL:Select DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), GETDATE()),('${CustomPollerStatus.DateTime}'))}

I hope this helps someone else

How to convert UTC date to local time in an alert message

Top Replies