Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Syntax error converting datetime from character string

Posted on 2008-06-16
7
Medium Priority
?
212 Views
Last Modified: 2010-03-20
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'.



0
Comment
Question by:benissitt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
7 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 21794582
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
 

Author Comment

by:benissitt
ID: 21794796
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 21794832
>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
 

Author Comment

by:benissitt
ID: 21794883
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1000 total points
ID: 21796082
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

609 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