Pass Through Query Formatting

You can use CAST or CONVERT.  For example:

CAST(MyDateString AS datetime)

Yes, that works.  Unfortunately I am finding that sometimes there is no value which then causes all values in the pass through result to change to #Error.  Can that be accomodated somehow?

Are these "no value" records null?  A zero length string?  All spaces?  Text not convertible to a date?  Some combination thereof?

For such cases, what would you want the return value to be?

They are null and they can stay as nulls.... just don't want them to cause everything else to error.

Also not sure if this matters or not, but I see some entries as 3/15/2011 some as 3/15/11

There is no way to control the input on the business system front end to force any kind of uniformity...

Thx

The best you can do is something like this:
CASE ISDATE(YourTextColumnGoesHere)
      WHEN 1 THEN CAST(YourTextColumnGoesHere as datetime)
      ELSE NULL
END

But even that is not bullet-proof.

How would I incorporate that into a pass through.   Below is the pass through in question, 'Customer_Defined_Value' is the field.

SELECT             ORDER_HEADER.Company_Code,ORDER_HEADER.Customer_No,  ORDER_HEADER.Order_No,CUSTOMER_DEFINED.Field_Name,CUSTOMER_DEFINED.Customer_Defined_Value
                         
FROM               ORDER_HEADER  INNER JOIN CUSTOMER_DEFINED_HDR_INFO ON (ORDER_HEADER.Order_No = CUSTOMER_DEFINED.Order_No) 
                         
WHERE           (ORDER_HEADER.Date_Entered BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE()) AND (ORDER_HEADER.Customer_No = 'UNI052') AND (CUSTOMER_DEFINED_HDR_INFO.Field_Name = 'DELIVERY DATE')

Select allOpen in new window

Try it this way:

SELECT  o.Company_Code,
        o.Customer_No,
        o.Order_No,
        c.Field_Name,
        -- c.Customer_Defined_Value,
		CASE ISDATE(c.Customer_Defined_Value)
			WHEN 1 THEN CAST(c.Customer_Defined_Value as datetime)
			ELSE NULL
		END Customer_Defined_Value
FROM    ORDER_HEADER o
        INNER JOIN CUSTOMER_DEFINED_HDR_INFO c ON o.Order_No = c.Order_No
WHERE   o.Date_Entered BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE()
        AND o_HEADER.Customer_No = 'UNI052'
        AND c.Field_Name = 'DELIVERY DATE'

Select allOpen in new window