AnsweredAssumed Answered

PowerShell: Issue passing TimeStamp into PI OLEDB Enterprise query

Question asked by Rashmi on Jan 10, 2016
Latest reply on Jan 20, 2016 by pthivierge
Branched from an earlier discussion

I'm facing the a time stamp issue for the following query.

 

$queryTimeStamp = "{0:dd/MM/yyyy HH:mm:ss}" -f $timeStamp

SELECT eh.Name towercode,tc1.height,tc."100speed",tc."90speed",tc."80speed1",tc."80speed2",

 

tc."60speed",tc."30speed",tc."10speed",tc."100direction",tc."90direction",tc."60direction" ,

tc."30direction",tc."10direction" ,tc."80temperature",tc."10temperature",tc."80humidity",

tc."10humidity",tc."80pressure",tc."10pressure"

FROM(SELECT Date('" + $queryTimeStamp + "')Time) t, [HYREF].[Asset].[ElementHierarchy] eh

    CROSS APPLY [HYREF].[DataT].[TransposeArchive_WindTowerTemplate](eh.ElementID, t.Time,t.Time) tc

    CROSS APPLY [HYREF].[DataT].[TransposeSnapshot_WindTowerTemplate](eh.ElementID)tc1

    WHERE eh.Path like '%\%' and eh.Name ='F4401W0001H0060'

    OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

 

When I run the above SQL in powershell script, the  timestamp prints incorrect way:

 

ForEach-Object : Exception calling "Open" with "4" argument(s): "[PI SDK] The time is invalid." + 10/01/201

""

At D:\Windpower\SSE\PI_Windpower_Script.ps1:35 char:11

+ 0..143 | % <<<<  {

    + CategoryInfo          : NotSpecified: (:) [ForEach-Object], MethodInvocationException

    + FullyQualifiedErrorId : ComMethodTargetInvocation,Microsoft.PowerShell.Commands.ForEachObjectCommand

 

The below lines are printing in From clause (look at the date format),

 

FROM(SELECT Date('" + 10/01/2016 00:00:00 + "') Time) t, [HYREF].[Asset].[ElementHierarchy] eh

 

    CROSS APPLY [HYREF].[DataT].[TransposeInterpolateDiscrete_WindTowerTemplate](eh.ElementID, t.Time) tc

    WHERE eh.Path like '%\%' and eh.Name like 'F4401%'

    OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

But when I hardcode the value as 10/01/2016 or any other day, the script is running OK. Any suggestion pls? The same variable ($queryTimestamp) executes OK for other SQLs although.

 

Regards,Rashmi

Outcomes