使用CTE的TSQL语句的执行顺序

Question

我正在为一个通用的表运行一个报告。因此，"ParameterValue“字段中的值将包含许多不同类型的数据。我想要做的是，只有在"ParameterName“列等于”Historian Timestamp“时才执行转换。

这就是我正在运行的查询...

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

它返回以下数据

╔═══════════╦═══════════╦═══════════════╦══════════════════╦═══════════════════╦═══════════╦══════════╦═══════════════════════════╦═════════════════════╦════════════════════════════════════╗
║ 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 ║
╚═══════════╩═══════════╩═══════════════╩══════════════════╩═══════════════════╩═══════════╩══════════╩═══════════════════════════╩═════════════════════╩════════════════════════════════════╝

但是，当我在CTE列"HistorianTimestamp“上添加一个过滤器时。我得到以下错误。

从字符串转换日期和/或时间时，Msg 241，Level 16，State 1，Line 1转换失败。

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')

我认为这可能是因为执行引擎决定在where子句之前执行select语句。这不是错的吗？执行引擎不应该考虑语句的执行顺序吗？即where子句在selects之前应用。如果不是，我如何重写该语句？