Hi! I wrote the following script that restricted just fine for dates prior to 10/1/9. So for example,
FROM st_CustomDataFieldsinTickets s1
JOIN st_CustomDataFieldsinTickets s3 on s1.TicketID = s3.TicketID and s3.customFieldID = 3
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 '09 at 09:23 AM