I went into Set Up IM Options -> Miscellaneous tab -> Custom Fields and added an optional custom field called SDSDate and set it to be a Data Type of Date.

Then in Maintain Items I set an SDSDate value for some items.

These custom field values are obviously stored in timItem.  There can be up to six custom field values set up, and all are stored in timItem as varchar(15).  Even if a custom field is set up as a "Date" Data Type, it is stored as a varchar in timItem.

I created a copy of vdvShipmentLines and I'm trying to modify this copy of the view so that I can use UserFld1 in a comparison.  I added timItem_1.UserFld1 to the view by simply checking the checkbox for that field.  Then in the text of the view I changed "timItem_1.UserFld1" to "CONVERT(Date, timItem_1.UserFld1) AS SDSDate."  That's all basic stuff.  Having done that, I would think I should be able to do something like this in a query.

SELECT * from vdvShipmentLine_custom
WHERE SDSDate IS NOT NULL AND ShipDate > SDSDate

However, that fails and returns:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Is this because some item records contain NULLs in UserFld1, and a date comparison obviously can't be done on a NULL value?  That is why I included "IS NOT NULL," but maybe that approach doesn't work.  I found a forum comment saying that even if you exclude records with NULL values in the WHERE clause PRIOR to attempting the date comparison/calculation, the NULL values in the table will still cause it to fail.  I don't know if that's true, but it seems plausible.

I have tried using both CAST and CONVERT in the view without success.

Has anyone ever added a Date custom field in Maintain Items and then used that custom field in a comparison or calculation in an SQL statement?

Using version 7.70.2 and SQL 2008 R2.

Thanks for any help.