Converting BigInt Date Values

cehottle

Does anyone know how to use T-SQL to convert the bigint date values in the RedGateMonitor database to datetime? An example would be 634250127993781348 as the date value for an entry in the Alert_Severity table. I've tried a number of suggestions that I've found and they all cause an overflow error. Thanks.

Philw

The magic you are looking for is

SELECT utils.TicksToDateTime(634250127993781348);

Have you seen that there are views which can be handy when writing queries by hand? They join the tables up to the root, and also provide the date times in both the Monitor internal representation and a SQL datetime. This saves lots of manual joins. For example to view the error logs of a SQL Server instance:

SELECT TOP 1000 
      [Cluster_SqlServer_Error_LogDate_DateTime]
      ,[Cluster_SqlServer_Error_SequenceNumber]
      ,[Cluster_SqlServer_Error_ProcessInfo]
      ,[Cluster_SqlServer_Error_Text]
      ,[CollectionDate_DateTime]
      ,[Cluster_Name]
      ,[Cluster_SqlServer_Name]
  FROM [SqlMonitorData].[data].[Cluster_SqlServer_Error_Instances_View]
  WHERE Cluster_Name = '<machine>' AND Cluster_SqlServer_Name = 'sql2005'
  ORDER BY Cluster_SqlServer_Error_LogDate, Cluster_SqlServer_Error_SequenceNumber;

cehottle

Thanks. I was looking for a view of alert information that had the T-SQL date, but didn't see one. I looked at Alert_Current. This will help a lot. I didn't know anything about Unix epoch time until yesterday.