sql query string/date fields

[le1234]

any chance anyone can help with the following query. I am having trouble with the where statement. I have a field CrewReported which is a free text string field that users enter a date into. i need to use this field as a date in my where criteria. when i do i get an error saying 'Invalid Column Name CrewReported'.
Thanks


SET DATEFORMAT dmy
SELECT FormInstanceID,
CrewOpened,
CrewRemoved,
Reporter,
Subject,
isnull(TypeOfWaste,'No Waste Type Recorded') TypeOfWaste,
isnull(SizeOfWaste,'No Size Recorded') SizeOfWaste,
CASE_Reference,
CREATED_DT,
CLOSED_DT,
ClassType,
Queue,
CaseStatus,
Minutes=DATEDIFF (n,CrewOpened,CrewRemoved)
FROM
(SELECT
CFI.FormInstanceID,
--The following subqueries are required to extract particular rows from LGNCC_FORMDATAITEM
--Also the date conversion is necessary as the date is stored as a string in this column
(select convert(SMALLDATETIME,SUBSTRING(VALUE,9,2) + SUBSTRING (VALUE,5,4)+ SUBSTRING (VALUE,1,4)+SUBSTRING (VALUE,11,9),103)
from lagandev.LGNCC_FORMDATAITEM where CFI.formInstanceid=formInstanceid and itemkey ='dateReported') CrewOpened,
(select convert(SMALLDATETIME,SUBSTRING(VALUE,9,2) + SUBSTRING (VALUE,5,4)+ SUBSTRING (VALUE,1,4)+SUBSTRING (VALUE,11,9),103)
from lagandev.LGNCC_FORMDATAITEM where CFI.formInstanceid=formInstanceid and itemkey ='dateRemoved') CrewRemoved,
(select value from lagandev.LGNCC_FORMDATAITEM where CFI.formInstanceid=formInstanceid and itemkey ='reporter') Reporter,
(select value from lagandev.LGNCC_FORMDATAITEM where CFI.formInstanceid=formInstanceid and itemkey ='Subject') Subject,
(select value from lagandev.LGNCC_FORMDATAITEM where CFI.formInstanceid=formInstanceid and itemkey ='TypeOfWaste') TypeOfWaste,
(select value from lagandev.LGNCC_FORMDATAITEM where CFI.formInstanceid=formInstanceid and itemkey ='Size') SizeOfWaste,
FCED.CASE_Reference,
FCED.CREATED_DT,
FCED.CLOSED_DT,
FCD.Type ClassType,
FCQD.name Queue,
FCED.Status CaseStatus
FROM
LAGANDEV.FLODS_CASE_ENQUIRY_F00 FCEF
INNER JOIN lagandev.FLODS_CASE_ENQUIRY_D00 FCED on FCEF.CASE_ENQUIRY_ID = FCED.FLODS_ID
INNER JOIN lagandev.FLODS_CASE_QUEUE_D00 FCQD on FCEF.QUEUE_ID = FCQD.FLODS_ID
INNER JOIN lagandev.FLODS_CLASSIFICATION_D00 FCD on FCEF.CLASSIFICATION_ID = FCD.FLODS_ID
INNER JOIN lagandev.lgncc_caseforminstance as CFI on FCED.CASE_Reference=CFI.CaseID
WHERE 1=1
---AND CrewRemoved > @stDate --These two lines are to deal with the hours min secs part
---AND CrewRemoved - 1 < @enDate
AND FCED.Status = 'Closed'
AND FCD.Type = 'Crew Report - FlyTip') A
ORDER BY TypeOfWaste,CASE_Reference

 

[dbomrrsm]

if you are getting an invalid column name error then the column doesnt exist its not because of an evaluation against a date.

when you sort that out to evaluate it with a date you need to ensure both the evaluation date and the CrewReported field are of the same format - take a look in books on-line at CONVERT - basically convert the CrewReported to the format of the date format of the parameter.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[SQLBill]

The WHERE clause is part of the derived table. You probably need to qualify the column name since the derived table is using several different tables.

-SQLBill

Posting advice: FAQ481-4875

 

[dbomrrsm]

I think SQLBill is on the right lines but I think it is because you have nothing called CrewReported in the inner Select statement and as TABLE A (supposedly containing colunm CrewReported )is being selected from you get the Invalid Column error.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]