cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 10

How does the DateTime variable do the calculation of dates that makes it floating like this?

Hi Everyone,

Please I like to find out how the DateTime variable does its calculation of dates in such a way that it returns a value as the one highlighted (in blue) in the code below.

The Relative Time Frame in the Orion Report Writer was set to "Show data over the past 14 hours" and the todays date is February 03, 2014.

SELECT  TOP 10000 Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
AVG(InterfaceAvailability.Availability) AS AVERAGE_of_Availability,
Nodes.Location AS Location

FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceAvailability ON (Interfaces.InterfaceID = InterfaceAvailability.InterfaceID AND Nodes.NodeID = InterfaceAvailability.NodeID)


WHERE
( DateTime BETWEEN 41671.1732407407 AND 41671.7916666667 )
AND 
(
  (Interfaces.Caption LIKE '%Tunnel%%')
)


GROUP BY Nodes.Caption, Interfaces.Caption, Nodes.Location


ORDER BY 1 ASC


Labels (3)
0 Kudos
1 Reply
Level 13

Hello,


When you click Show SQL in report writer, datetime pick Time frame and recalculate interval of datetime.

You can see recalculation after you click "Execute SQL query" in Report writer. Every click datetime is recalculated.

Datetime can be converted to float and vice versa. If you use report writer template you can pick timeframe and report writer convert it himself.

Floating number representation of a DateTime in SQL server is a number of days since the start of the epoch (since 01-01-1900) in the integer part and the fraction part is the part of the day elapsed since midnight.

See e.g. http://stackoverflow.com/questions/4141399/how-sqls-convert-function-work-when-converting-datetime-t...

Thanks