Solved

Syntax error converting datetime from character string

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

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

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.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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