Solved

SQL Server 2005 SQL Error

Posted on 2009-04-01
3
174 Views
Last Modified: 2012-05-06
The below SQL is producing the following error:

Msg 207, Level 16, State 1, Line 21
Invalid column name 'StartDate'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'ClosingDateFinal'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'ClosingDateF2'.

Any ideas?
CASE WHEN [dtStart] IS NULL THEN NULL 

      ELSE CAST(MONTH([dtStart]) AS varchar(2)) + '/1/' + 

               CAST(YEAR([dtStart]) AS varchar(4)) + ' 5:00 PM' END AS StartDate,

CAST([StartDate] AS datetime) AS StartDate2,

CASE WHEN StartDate Is Null THEN Null ELSE [StartDate2] END AS Dt3

Open in new window

0
Comment
Question by:CipherIS
3 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24043598
I am assuming this is the select column list in a query.  startdate looks to be a column alias and you can not use it on another line.  You will have to reuse the entire logic for startdate or rework the query some, but there is not enough detail here to help with that.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24043618
StartDate and StartDate2 are not columns in your table, they are aliases in your select statement.  They cannot be referenced in the statement itself unless you place the select into a derived table.  From there, the statements can reference by alias.

example:
select CASE WHEN StartDate Is Null THEN Null ELSE [StartDate2] END AS Dt3

from

(select CAST([StartDate] AS datetime) AS StartDate2

from 

(select CASE WHEN [dtStart] IS NULL THEN NULL 

      ELSE CAST(MONTH([dtStart]) AS varchar(2)) + '/1/' + 

               CAST(YEAR([dtStart]) AS varchar(4)) + ' 5:00 PM' END AS StartDate

from TableA) a)b)c

Open in new window

0
 
LVL 8

Expert Comment

by:vinurajr
ID: 24043780
IS THE CODE U SEND IS CORRECT.. WHAT IS UR REQUIREMENT...?
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now