using Convert in SQL

[sc5502]

Background: MS SQL Server 2008
Experience Level (1-10 with 10 being expert): 5

I know how to take a field that is defined in SQL server as datetime and extract the date only value.

SELECT  Convert(varchar(2),DATEPART(MONTH, regi_datetimein)) + '/' + Convert(varchar(2),DATEPART(DAY, regi_datetimein)) + '/' + Convert(varchar(4),DATEPART(year, regi_datetimein)) as v_date 
from [DB04_Visitors].[dbo].[T040200_REGISTRATION]

I get the following results:

v_date
8/30/2017
8/30/2017
8/31/2017
8/31/2017

What I need to do is take v_date and compare to today's date (which is 8/31/2017). I have tried this

SELECT  Convert(varchar(2),DATEPART(MONTH, regi_datetimein)) + '/' + Convert(varchar(2),DATEPART(DAY, regi_datetimein)) + '/' + Convert(varchar(4),DATEPART(year, regi_datetimein)) as v_date 
from [DB04_Visitors].[dbo].[T040200_REGISTRATION]
where v_date='8/31/2017'

and get this as a result:

Messages
Msg 207, Level 16, State 1, Line 3
Invalid column name 'v_date'.

What am I doing wrong?

Thank you in advance.

[Luk3r]

Your code is reading v_date as a column in the table, when in fact, it is not a column within the table. Try this and let us know if you have any luck!

SELECT A.v_date
FROM (
SELECT  Convert(varchar(2),DATEPART(MONTH, regi_datetimein)) + '/' + Convert(varchar(2),DATEPART(DAY, regi_datetimein)) + '/' + Convert(varchar(4),DATEPART(year, regi_datetimein)) as v_date 
FROM [DB04_Visitors].[dbo].[T040200_REGISTRATION]
) AS A
WHERE A.v_date='8/31/2017'

[Rabbit]

So 3 things.

First, you can't reference an alias created in the SELECT clause in its own WHERE clause, it's out of the execution scope. The simplified execution order is:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Your alias in the SELECT clause isn't available until after step 5.

Second, be careful with date comparisons when using strings. It might not shake out the way you think. If you're going to compare dates, then use date types. It's fine to use the strings in the return value to format it the way you want, but don't use strings when it should be a date in the WHERE clause.

Third, the convert function has an optional style parameter, there's no need to extract each part and concatenate them together. This will give you the same thing CONVERT(VARCHAR (10), regi_datetimein , 101)