>>>>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.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham