Filtering on Alias date in "WHERE clause

Transact-SQL
1

Hi Gents,

I have a date ( yr,day,month) that I want to use this date at "WHERE" clause to filter anything >= 1/1/20
is this correct? can I use an alias date to do this?

Select

  lname as [Last Name],							
  fname as [First Name],
  patient_nbr,

convert(varchar, admin_year) +'-' + right('0' + convert(varchar, admin_month), 2)
+'-'+ right('0' + convert(varchar, admin_day), 2) as [/Completion Date]

from my table
where [completion date] >= 1/1/20 ??

I get Invalid column name 'Completion Date'.

Thanks.
Pasi.

2

SELECT is logically processed after WHERE:

https://www.sqlservercentral.com/blogs/sql-server-logical-query-processing.

Some options are:

SELECT T.lname, T.fname, T.patient_nbr
FROM MyTable T
	CROSS APPLY
	(
		VALUES(CAST(T.admin_year * 10000 + T.admin_month * 100 + T.admin_day AS char(8)))
	) X (CompletionDate)
WHERE X.CompletionDate >= '20200101';
WITH ExtraCols
AS
(
	SELECT lname, fname, patient_nbr
		,CAST(T.admin_year * 10000 + T.admin_month * 100 + T.admin_day AS char(8)) AS CompletionDate
	FROM MyTable T
)
SELECT lname, fname, patient_nbr, CompletionDate
FROM ExtraCols
WHERE CompletionDate >= '20200101';
1 Like
3

Hi Pasi,

You cannot use an alias in the WHERE clause because of the SQL engine is handeling a query in a specific order. I also would suggest to use datefrompart function.

WHERE convert(varchar, admin_year) +'-' + right('0' + convert(varchar, admin_month), 2)
+'-'+ right('0' + convert(varchar, admin_day), 2) >= DATEFROMPARTS(year, month, day);

1 Like
4

You can use CROSS APPLY to generate the date and filter by that date:

Select
  lname as [Last Name],							
  fname as [First Name],
  patient_nbr,
  dt.completion_date
from mytable
cross apply (values (datefromparts(admin_year, admin_month, admin_day))) As dt(completion_date)
where dt.completion_date >= '20200101'

Assuming you want a range of dates:

Select
  lname as [Last Name],							
  fname as [First Name],
  patient_nbr,
  dt.completion_date
from mytable
cross apply (values (datefromparts(admin_year, admin_month, admin_day))) As dt(completion_date)
where dt.completion_date >= '20200101'
and dt.completion_date < '20200201'

Better yet - if possible - create a new column on the table as a computed persisted column using datefromparts and add an index. Then you can just query the computed column as needed.

2 Likes
5

Thanks Jeff! great solution!
Pasi.

6

Thanks I figured that was not possible.
Pasi.

7

Thanks Ifor, didn't think of CTE, good solution!
Pasi