CODEWITHSUNDEEP
sqlsql-servert-sqldatetimewhere-clause
Aniello Lettieri
Aniello Lettieri

Reputation: 23

Using DATETIME in WHERE clause in SQL Server

I have this query

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

OUTPUT (ok):

+-----------+-----------+-------------------------+
| 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 |
+-----------+-----------+-------------------------+

I want to add a condition on date; to avoid error in internal datetime coding, i use the DATETIMEFROMPARTS function like that

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 > DATETIMEFROMPARTS(2018,4,9,0,0,0,0) and dt <  DATETIMEFROMPARTS(2018,5,9,0,0,0,0))
ORDER BY dt ASC,StateAfter ASC

ERROR:-

Messaggio 207, livello 16, stato 1, riga 5
Invalid column name 'dt'.
Messaggio 207, livello 16, stato 1, riga 5
Invalid column name 'dt'.

Can someone help me to understand why it doesn't work? I tried also the BETWEEN clause; Thanks

Upvotes: 2

Views: 5796

Answers (4)

Dhinagaran P
Dhinagaran P

Reputation: 51

This error happned because dt is not a physical column.

You can achieve like below query:

    SELECT tbl.* FROM ( 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 ) tbl
    WHERE tbl.[MsgNumber] IN (5,9,13,17) 
AND (tbl.dt > DATETIMEFROMPARTS(2018,4,9,0,0,0,0) AND tbl.dt <  DATETIMEFROMPARTS(2018,5,9,0,0,0,0)) 
    ORDER BY 
        tbl.dt ASC, tbl.StateAfter ASC

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

You Are getting this error Because in your Table there is no Such a Column dt. dt is Just an Alias Name that you have given in the select so its not a Column. So You Can use any of the below methods :

Using CTE or SubQuery (Recommended )

; with CTE
AS
(
    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
)
SELECT
    *
    FROM CTE
WHERE [MsgNumber] IN (5,9,13,17) 
    AND 
(
dt > DATETIMEFROMPARTS(2018,4,9,0,0,0,0) and dt <  DATETIMEFROMPARTS(2018,5,9,0,0,0,0))
    ORDER BY dt ASC,StateAfter ASC

Giving the Formula in Where Condition (Not Recommended )

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 
(
    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)
                > DATETIMEFROMPARTS(2018,4,9,0,0,0,0) 
    and 
    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)
                <  DATETIMEFROMPARTS(2018,5,9,0,0,0,0))
ORDER BY dt ASC,StateAfter ASC

Upvotes: 0

apomene
apomene

Reputation: 14389

Alias dt can not be used inside WHERE clause. Use a subquery like:

 SELECT t.[MsgNumber]
    ,t.[StateAfter]
    ,t.dt
FROM (
    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
    ) t
WHERE [MsgNumber] IN (
        5
        ,9
        ,13
        ,17
        )
    AND (
        dt > DATETIMEFROMPARTS(2018, 4, 9, 0, 0, 0, 0)
        AND dt < DATETIMEFROMPARTS(2018, 5, 9, 0, 0, 0, 0)
        )
ORDER BY dt ASC
    ,StateAfter ASC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You cannot refer to a column alias in the where clause. The typical solutions are to use subqueries or CTEs. But SQL Server has another method that I like, apply:

SELECT [MsgNumber], [StateAfter], v.dt
FROM TABLE t CROSS APPLY
     (VALUES (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)
             )
     ) V(dt)
WHERE [MsgNumber] IN (5, 9, 13, 17) AND
      v.dt > DATETIMEFROMPARTS(2018, 4, 9, 0, 0, 0, 0) AND
      v.dt <  DATETIMEFROMPARTS(2018, 5, 9, 0, 0, 0, 0))
ORDER BY dt ASC,StateAfter ASC;

I find it curious that you don't use the simpler:

WHERE [MsgNumber] IN (5, 9, 13, 17) AND
      v.dt > '20180409'  AND
      v.dt < '20180509'

(I did not use hyphens because this format will always be interpreted as YYYYMMDD regardless of internationalization settings.)

Upvotes: 4

Related Questions