Syntax error converting datetime from character string

I am getting the error message with the following code:

Case when 'firstinvoicedate' = dateadd(month,-14,getdate()) then 'new work' else 'rolling work' end as 'Work Status'.



benissittAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If you put single quotes around 'firstinvoicedate', then you are comparing the string 'firstinvoicedate', and not the column firstinvoicedate.
Is that what you are trying to do?

else

Case when firstinvoicedate = dateadd(month,-14,getdate()) then 'new work' else 'rolling work' end as 'Work Status'.
0
 
benissittAuthor Commented:
I'll show you the entire query to clear things up for you
SELECT yourquery.*,FirstInvoiceDate FROM
	(select 
jobheader.jobno,
costing.dbo.revenue.docketdate,
companyname,
jobdesc,
jobheader.status,
costing.dbo.revenue.value,
orderheader.completedate,
salesperson,
Case	when 'firstinvoicedate' = dateadd(month,14,getdate()) then 'old work' else 'mew work' end as ' '
from jobheader
inner join orderheader on jobheader.orderno = orderheader.orderno
inner join customers.dbo.company on orderheader.customer = customers.dbo.company.coid
inner join costing.dbo.revenue on jobheader.jobno = costing.dbo.revenue.jobno
INNER join customers.dbo.lookupdesignation on customers.dbo.company.designation = customers.dbo.lookupdesignation.ldcode
where costing.dbo.revenue.docketdate is not null ) yourquery
INNER JOIN
	(SELECT CompanyName,Min(docketdate) AS FirstInvoiceDate
	FROM
		(select 
jobheader.jobno,
costing.dbo.revenue.docketdate,
companyname,
jobdesc,
jobheader.status,
costing.dbo.revenue.value,
orderheader.completedate,
salesperson,
Case when firstinvoicedate = dateadd(month,-14,getdate()) then 'new work' else 'rolling work' end as 'Work Status'
from jobheader
inner join orderheader on jobheader.orderno = orderheader.orderno
inner join customers.dbo.company on orderheader.customer = customers.dbo.company.coid
inner join costing.dbo.revenue on jobheader.jobno = costing.dbo.revenue.jobno
INNER join customers.dbo.lookupdesignation on customers.dbo.company.designation = customers.dbo.lookupdesignation.ldcode
where costing.dbo.revenue.docketdate is not null ) yourquery
	GROUP BY CompanyName) latest
ON
yourquery.CompanyName=latest.CompanyName

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I'll show you the entire query to clear things up for you
My comment is already pretty clear.

>SELECT CompanyName,Min(docketdate) AS FirstInvoiceDate
Here you have an expression aliased as FirstInvoice Date, with the table aliased as latest, so your expression should be...

Case when latest.firstinvoicedate = dateadd(month,-14,getdate()) then 'new work' else 'rolling work' end as 'Work Status'
0
 
benissittAuthor Commented:
I tried that before but I get the following error messages:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'latest' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'latest' does not match with a table name or alias name used in the query.
0
 
Mark WillsTopic AdvisorCommented:
No, you cannot access that way.

It is a subquery that you are joining to the "latest" subquery (which is almost identical the the other subquery). Seems fairly convaluted... Each time I go to read it, I get a headache :)

What happens when you make the first one just like the second one ? assume that firstinvoicedate is a column somewhere - although it is not prefixed with a table or alias like the other columns...



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.