Order of execution for TSQL statement with CTE

Question

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 to only perform the conversion if the "ParameterName" column is equal 'Historian Timestamp'.

This is the query that I am running...

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
), 
Comments AS (
    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')
SELECT * FROM COMMENTS

Which Returns the following data

╔═══════════╦═══════════╦═══════════════╦══════════════════╦═══════════════════╦═══════════╦══════════╦═══════════════════════════╦═════════════════════╦════════════════════════════════════╗
║ LogbookId ║ CommentId ║ CommentTypeId ║     Comment      ║ CommentDetailTime ║ FirstName ║ LastName ║      ParameterValue       ║        Name         ║         HistorianTimestamp         ║
╠═══════════╬═══════════╬═══════════════╬══════════════════╬═══════════════════╬═══════════╬══════════╬═══════════════════════════╬═════════════════════╬════════════════════════════════════╣
║         1 ║         2 ║             1 ║ I entered 1      ║ 53:39.8           ║ Jason     ║ Turan    ║ 2016-11-29T12:47:14       ║ Historian Timestamp ║ 2016-11-29 12:47:14.0000000 +00:00 ║
║         1 ║        54 ║             1 ║ Note on tablet.  ║ 42:01.8           ║ Jason     ║ Turan    ║ 2016-12-05T13:36:34       ║ Historian Timestamp ║ 2016-12-05 13:36:34.0000000 +00:00 ║
║         1 ║        55 ║             1 ║ testnotes        ║ 47:37.7           ║ Desiree   ║ Teter    ║ 2016-12-07T15:13:29       ║ Historian Timestamp ║ 2016-12-07 15:13:29.0000000 +00:00 ║
║         4 ║        56 ║             1 ║ notes            ║ 09:16.4           ║ Desiree   ║ Teter    ║ 2016-12-08T14:00:56       ║ Historian Timestamp ║ 2016-12-08 14:00:56.0000000 +00:00 ║
║         4 ║        56 ║             1 ║ notes 2          ║ 09:39.5           ║ Desiree   ║ Teter    ║ 2016-12-08T14:00:56       ║ Historian Timestamp ║ 2016-12-08 14:00:56.0000000 +00:00 ║
║         4 ║        57 ║             1 ║ ?                ║ 36:19.2           ║ Desiree   ║ Teter    ║ 2016-12-08T14:00:56       ║ Historian Timestamp ║ 2016-12-08 14:00:56.0000000 +00:00 ║
║         4 ║        59 ║             1 ║ testnotes sdfsdf ║ 29:42.1           ║ Desiree   ║ Teter    ║ 2016-12-08T14:00:56-06:00 ║ Historian Timestamp ║ 2016-12-08 14:00:56.0000000 -06:00 ║
╚═══════════╩═══════════╩═══════════════╩══════════════════╩═══════════════════╩═══════════╩══════════╩═══════════════════════════╩═════════════════════╩════════════════════════════════════╝

However when I add a filter on the CTE Column "HistorianTimestamp". I get the following error.

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

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
), 
Comments AS (
    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')
SELECT * FROM COMMENTS
WHERE HistorianTimestamp > CONVERT(DATETIMEOFFSET, '2016-11-29T00:00:00-06:00') AND HistorianTimestamp <  CONVERT(DATETIMEOFFSET, '2016-11-30T00:00:00-06:00')

I'm thinking it's probably due to the execution engine deciding to do the select statement before the where clause. Isn't that wrong though? Shouldn't the order of execution of statements be respected by the execution engine? IE the where clause is applied before selects. If not how can I rewrite the statement?

Answer 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.