Hi! I wrote the following script that restricted just fine for dates prior to 10/1/9. So for example,
select s1.TicketID, s3.DataContent as [Requester], s5.DataContent as [Department], s7.DataContent as [Program], s10.DataContent as [Category], s11.DataContent as [Resolution], s12.DataContent as [Cause], s14.DataContent as [Description of Problem], s16.DataContent as [Description of Resolution], s17.DataContent as [Total Ticket Time], s18.DataContent as [Assigned To?], s19.DataContent as [TS Start Time], s20.DataContent as [TS End Time]
FROM st_CustomDataFieldsinTickets s1
JOIN st_CustomDataFieldsinTickets s3 on s1.TicketID = s3.TicketID and s3.customFieldID = 3
WHERE s1.customFieldID = 1 and s19.DataContent >= '10/1/9' and s19.DataContent <= DATEADD(day,1,'10/30/9')
Ok - the vendor provided a completely generic table with all content datatypes as nvarchar. that is unfortunate. Please post some dataContent values for customerFieldID = 19 so we can see if the value can be directly converted to datetime. If they can be then you can say,
otherwise you'll have to manipulate the string to something that can be converted to datetime. Another thing I'd suggest is replace all those JOINs to different instances of the same table with something like this:
EDIT: Assuming your comment is accurate and that first two parts ("199 19.") are not part of the dataContent value it looks like one of your values is not well formed. It is missing a "/" separator. You would need to exclude those value or clean them up. That is one of the problems of storing data in untyped columns.
So, if one row of the dataContent looks like this : 192 19 10/1/2009 09:45 AM
You could do:
If this doesn't work, can you post any that are different to this format?
answered Nov 20, 2009 at 09:23 AM