title: Using DATETIME in WHERE clause in SQL Server
body: <p>I have this query</p>
  
  <pre><code>SELECT  
      [MsgNumber], [StateAfter],
      DATETIMEFROMPARTS (SUBSTRING([TimeString], 7, 4), 
                         SUBSTRING([TimeString], 4, 2), 
                         SUBSTRING([TimeString], 1, 2),
                         SUBSTRING([TimeString], 12, 2), 
                         SUBSTRING([TimeString], 15, 2), 
                         SUBSTRING([TimeString], 18, 2), 0) AS dt
  FROM 
      TABLE
  WHERE 
      [MsgNumber] IN (5, 9, 13, 17)
  ORDER BY 
      dt ASC, StateAfter ASC
  </code></pre>
  
  <p>OUTPUT (ok):</p>
  
  <pre><code>+-----------+-----------+-------------------------+
  | MsgNumber | tateAfter |           dt            |
  +-----------+-----------+-------------------------+
  |         9 |         1 | 2018-03-09 17:22:00.000 |
  |         9 |         0 | 2018-03-09 17:23:37.000 |
  |        17 |         1 | 2018-03-09 17:23:37.000 |
  |        17 |         1 | 2018-03-09 17:29:43.000 |
  |        17 |         1 | 2018-03-09 17:36:21.000 |
  +-----------+-----------+-------------------------+
  </code></pre>
  
  <p>I want to add a condition on date; to avoid error in internal datetime coding, i use the DATETIMEFROMPARTS function like that</p>
  
  <pre><code>SELECT [MsgNumber],[StateAfter]
  ,DATETIMEFROMPARTS ( SUBSTRING ( [TimeString] ,7 , 4 ), SUBSTRING ( [TimeString] ,4 , 2 ), SUBSTRING ( [TimeString] ,1 , 2 ),
                      SUBSTRING ( [TimeString] ,12 , 2 ), SUBSTRING ( [TimeString] ,15 , 2 ), SUBSTRING ( [TimeString] ,18 , 2 ) , 0) as dt
  FROM TABLE
  WHERE [MsgNumber] IN (5,9,13,17) AND (dt &gt; DATETIMEFROMPARTS(2018,4,9,0,0,0,0) and dt &lt;  DATETIMEFROMPARTS(2018,5,9,0,0,0,0))
  ORDER BY dt ASC,StateAfter ASC
  </code></pre>
  
  <p>ERROR:-</p>
  
  <blockquote>
    <p>Messaggio 207, livello 16, stato 1, riga 5 <BR>
    Invalid column name 'dt'. <BR>
    Messaggio 207, livello 16, stato 1, riga 5 <BR>
    Invalid column name 'dt'.</p>
  </blockquote>
  
  <p>Can someone help me to understand why it doesn't work? I tried also the BETWEEN clause; Thanks</p>