By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,377 Members | 1,665 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,377 IT Pros & Developers. It's quick & easy.

using Convert in SQL

100+
P: 102
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.
Expand|Select|Wrap|Line Numbers
  1. 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 
  2. 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
Expand|Select|Wrap|Line Numbers
  1. 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 
  2. from [DB04_Visitors].[dbo].[T040200_REGISTRATION]
  3. 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.
Aug 31 '17 #1
Share this Question
Share on Google+
2 Replies


100+
P: 300
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!

Expand|Select|Wrap|Line Numbers
  1. SELECT A.v_date
  2. FROM (
  3. 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 
  4. FROM [DB04_Visitors].[dbo].[T040200_REGISTRATION]
  5. ) AS A
  6. WHERE A.v_date='8/31/2017'
  7.  
Sep 1 '17 #2

Rabbit
Expert Mod 10K+
P: 12,430
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)
Sep 1 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.