Using DATETIME in WHERE clause in SQL Server

Question

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

Gordon Linoff · Accepted Answer · 2018-05-07 10:50:01Z

4

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

answered May 7, 2018 at 10:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aniello Lettieri Over a year ago

I didn't used v.dt > '20180409' AND v.dt < '20180509' because I needed datetime in the condition; Can I use same logic writing "YYYYMMDDHHMMSS" ?

Gordon Linoff Over a year ago

@AnielloLettieri . . . SQL Server will translate '20180409' to a datetime, if appropriate. For a constant datetime, I would write it as YYYY-MM-DD HH:MI:SS.

apomene · Accepted Answer · 2018-05-07 10:50:12Z

0

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

answered May 7, 2018 at 10:50

apomene

14.4k10 gold badges51 silver badges76 bronze badges

Comments

Jayasurya Satheesh · Accepted Answer · 2018-05-07 10:52:38Z

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

Dhinagaran P · Accepted Answer · 2018-05-07 12:28:16Z

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

Collectives™ on Stack Overflow

Using DATETIME in WHERE clause in SQL Server

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related