Solved

Syntax error converting datetime from character string

Posted on 2008-06-16
7
202 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
Comment Utility
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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now