I'm trying to convert a DateTime (from System.Timestamp()) into a BIGINT or the string equivalent in Azure Stream Analytics. Unfortunately, the obvious method does not work:
CAST(System.Timestamp() AS BIGINT)
I just get the error:
Cannot cast value of type 'datetime' to type 'bigint' in expression 'CAST (System . Timestamp ( ) AS BIGINT)'.
My end goal is to just get either the ticks equivalent of the timestamp or some sort of formatted output such as yyyyMMddHHmmss that I want to use as a unique id.
You can use the DATEDIFF
function, with a difference in microseconds
to obtain unique values.
The following code provides a timestamp equivalent to ticks:
DATEDIFF(microsecond, '1970-01-01T00:00:00.0000000Z', System.Timestamp()) AS TicksEquivalent
Output: