>>>>Hi,
>>>>
>>>>My stored procedure has the following code:
>>>>

>>>>SET @TargetDate = CONVERT(datetime, @TargetDate)
>>>>

>>>>Which converts a string @TargetDate to a datetime value.
>>>>The above works when used with American customers
>>>>
>>>>But when using with a European customer where the string value of @TargetDate (before the conversion) is set by ToShortDateString(), the result is an error (The conversion of a varchar data type to a smalldatetime data type is out-of-range value).
>>>>
>>>>Is there a 3rd parameter I can use with the above CONVERT() function to get around the problem?
>>>>
>>>>TIA
>>>
>>>And this works?
>>>I doubt it, you can not redefine type of the variable from n/var/char to datetime.
>>
>>Maybe I didn't express it correctly. Here is more explanation.
>>In the stored procedure I define the variable
>>

>>declare @TargetDate AS SmallDateTime
>>

>>Then I set it to NULL
>>

>>select @TargetDate = null
>>

>>Then I extract the value from an XML variable as follows:
>>

>>SELECT @TargetDate = [Fld FldName].value('.','char(10)') FROM @XmlDocWo.nodes('/WORK_HIS/Fld') Fld([Fld FldName])   WHERE [Fld FldName].value('@name','varchar(20)') = 'DATE_TARG'
>>

>>Then I check if the value is NULL and set it according the the code below.
>>

>>IF @TargetDate IS NOT NULL
>>		BEGIN
>>			SET @TargetDate = CONVERT(datetime, @TargetDate)
>>		END 
>>

>>
>>It all works for American customers.
>>
>>But for Euro, where the XML gets the value into the /WORK_HIS/Fld from the British Date Format, error.
>>
>>UPDATE: I commented the code above and I still get the error. So, it must be somewhere else.
>
>Hmm, I wrote and lost a long description in my previous reply and then rewrote to send, missed this in between.
>
>With XML, dates are already passed in a specific format with timezone information (UTC). I am not sure about the purpose of this code.

Thank you for both replies. What happens is that if the value of @TargetDate is NULL, all works.
Or if the value of @TargetDate from the XML type is American format (that works for many customers), all works.
But when I set the value in the ASP.NET application to the XML element TargetDate, I get error.
Why I do it this way is a long story. But it works for many years.
Now I need to figure where it breaks for the British date.