I'm attempting to do a query from SQL Server using PIOLEDB linked server to get back records from the piinterp table where I want to get a rounded version of the time for the timestamp.
Here's the query I attempted but it is not working:
DECLARE @EndTime DATETIME
DECLARE @TimeStep nvarchar(20)
DECLARE @Tag nvarchar(60)
SET @StartTime = '2016-01-02 01:00:00 PM'
SET @EndTime = '2016-01-04 11:50:00 PM'
SET @TimeStep = '01:00:00'
SET @Tag = 'Mix'
SELECT *, (SELECT result FROM [USCLHISTORIAN].[pifunction]..[round5] WHERE arg1 = [time] AND arg2 = @ts) as "RoundedTime"
WHERE tag like '%' + @Tag +'%'
AND [time] > @StartTime AND [time] < @EndTime
AND [timestep] = (SELECT result FROM [USCLHISTORIAN].[pifunction]..[time] WHERE arg1 = @TimeStep)
I'm using the PIFUNCTION's:
I have the collation set to true on my linked server.
This query runs fine when the rounding isn't attempted, when I attempt the rounding I get NULL's in my RoundedTime column.
Any help with this would be wonderful.
Thank you in advance.