Solved

Syntax error converting datetime from character string

Posted on 2008-06-16
7
205 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
  • 2
  • 2
7 Comments
 
LVL 65

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 65

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.​
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 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