CODEWITHSUNDEEP
sqlsql-server
user3068747
user3068747

Reputation:

Reference an Aliased Column in Sql

I need to reference an aliased column in the sqlserver. here the 'dob' is the date of birth column from tbl_Student_Admission table

Select DATEPART(yy, dob) as years 
From dbo.tbl_Student_Admission 
Where years between '1990' and '1992'

Upvotes: 0

Views: 544

Answers (6)

slavoo
slavoo

Reputation: 6076

Solution with cross apply

SELECT t.*, c.y
FROM dbo.tbl_Student_Admission t
CROSS APPLY
(
 SELECT DATEPART(yy, t.years) as y
) c
WHERE c.y between 1990 and 1994

Upvotes: 0

whytheq
whytheq

Reputation: 35557

A CTE is an alternative:

WITH myCTE 
    AS
    (
    SELECT  years = DATEPART(yy, dob)
    FROM    dbo.tbl_Student_Admission
    )
SELECT  *
FROM    myCTE
WHERE   years BETWEEN 1990 AND 1992

Upvotes: 0

Aditya
Aditya

Reputation: 2301

There is a very simpler way. so why should go complex ? You should try like this. This will work.

Select DATEPART(yy, dob) as 'years' 
From dbo.tbl_Student_Admission 
Where DATEPART(yy, dob) between '1990' and '1992'

Upvotes: 3

Deepshikha
Deepshikha

Reputation: 10264

The first noticeable aspect of SQL that is different from other programming languages is the order in which the code is processed. In most programming languages, the code is processed in the order in which it is written. In SQL, the first clause that is processed is the 

FROM clause,then Where -> Group By -> Having-> Select -> Order By

so the SELECT clause, which appears first, is processed almost last. So you cannot refer aliased column years from select list in where clause because it's not calculated yet. You should write it as:

select [years] from (
select DATEPART(yy, dob) as years 
from dbo.tbl_Student_Admission 
) T
where T.years between 1990 and 1992

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30765

Just use a second SELECT:

select * from (
  select DATEPART(yy, dob) as years from dbo.tbl_Student_Admission
) v1
where years between '1990' and '1992'

Please note that by using a string for your BETWEEN, you're forcing the database to convert the results of DATEPART to a string, which will usually result in degraded performance. So you should use plain numbers instead:

select * from (
  select DATEPART(yy, dob) as years from dbo.tbl_Student_Admission
) v1
where years between 1990 and 1992

SQL Fiddle

Upvotes: 3

Vishal Patel
Vishal Patel

Reputation: 973

You can use it like this...

select * from (
select DATEPART(yy, dob) as years from dbo.tbl_Student_Admission 
) abc
where years between '1990' and '1992'

But this is not a good way... it will down your query performance. So you should use following query ..

select DATEPART(yy, dob) as years from dbo.tbl_Student_Admission where DATEPART(yy, dob) between '1990' and '1992'

Upvotes: 3

Related Questions