Order Of Execution For Tsql Statement With Cte

I'm working on running a report for a table that generic table. So the values in the 'ParameterValue' field will contain data from many different types. What I am trying to do is t

Solution 1:

First, whenever you have:

SELECT*FROM<someCTE>

That means you have too many CTEs IMHO.

You could simplify your query like this:

WITH LogbookSourceObjects AS (
    SELECT CAST(obj.NAME AS INT) as LogbookId, ObjectId 
    FROM PISourceObject obj
    JOIN PISource s ON s.SourceID = obj.SourceId
    WHERE s.Name ='DEDR' AND ISNUMERIC(obj.NAME) = 1
)
SELECT lso.LogbookId, 
       c.CommentId, 
       c.CommentTypeId, 
       cd.Comment, 
       cd.CommentDetailTime, 
       u.FirstName, 
       u.LastName,
       cp.ParameterValue, 
       p.Name, 
       CONVERT(DATETIMEOFFSET, cp.ParameterValue) AS HistorianTimestamp
FROM LogbookSourceObjects lso 
JOIN PIComment c ON c.ObjectId = lso.ObjectId
JOIN PICommentDetail cd ON cd.CommentId = c.CommentId 
JOIN PICommentType ct ON ct.CommentTypeId = c.CommentTypeId
JOIN PICommentParameter cp on cp.CommentId = c.CommentId 
JOIN PIParameter p on cp.ParameterId = p.ParameterId
JOIN PIUser u on u.UserId = cd.UserId 
WHERE p.Name ='Historian Timestamp'AND CONVERT(DATETIMEOFFSET, cp.ParameterValue) > CONVERT(DATETIMEOFFSET, '2016-11-29T00:00:00-06:00') AND CONVERT(DATETIMEOFFSET, cp.ParameterValue) < CONVERT(DATETIMEOFFSET, '2016-11-30T00:00:00-06:00');

This will almost certainly still fail but will make troubleshooting a little easier. You could then try (off the top of my head):

AND ISDATE(CONVERT(DATETIMEOFFSET, cp.ParameterValue))= 1

Or perhaps ...

AND TRY_CONVERT(DATETIMEOFFSET, cp.ParameterValue) > CONVERT(DATETIMEOFFSET, '2016-11-29T00:00:00-06:00') 
AND TRY_CONVERT(DATETIMEOFFSET, cp.ParameterValue) < CONVERT(DATETIMEOFFSET, '2016-11-30T00:00:00-06:00');

Just some food for thought.