Solved

Syntax error converting datetime from character string

Posted on 2008-06-16
7
208 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 250 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 250 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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. …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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