CODEWITHSUNDEEP
sqlsql-server
Gaduks
Gaduks

Reputation: 681

Apply filter to casted column

I would like to apply a filter to a column I've casted as DATE, as the example below:

select A, B, CAST(C as DATE) as D, E from MY_TABLE where D>'2018-12-12'

Which doesn't work. I get the error: "Invalid column name 'D'." I've also tried:

select A, B, CAST(C as DATE) as D, E from MY_TABLE where C>'2018-12-12'

Getting the same error. Any idea how I could do that?

Upvotes: 0

Views: 331

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

First, if C is already a date/time type, you can simply do:

select A, B, CAST(C as DATE) as D, E
from MY_TABLE 
where C > DATEADD(DAY, 1, '2018-12-12')

If not, you can use a lateral join, which in SQL Server uses the APPLY keyword:

select A, B, v.D, E
from MY_TABLE t cross apply
     (values (CAST(C as DATE))) v(D)
where v.D > '2018-12-12';

If C is a string, I strongly recommend TRY_CAST() or fixing the data in-place.

Upvotes: 1

Related Questions