>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
It is strange that this code works at all.
If the @TargetDate is a string then you would be converting that to a string, then back to a string with implicit conversion? Why would you ever want to do that?
If the @TargetDate is a DateTime, then why would you convert a DateTime to a Datetime again to store in a DateTime?
For conversion from a string to DateTime (Date\DateTime\DateTime2 ...), Convert has a 3rd parameter of "Style", however you shouldn't have a need to use that. If you want to store and convert a string value safely, regardless of date and language settings of the server, use the specific canonical format style, which is yyyyMMdd HH:mm:ss. ie:
declare @targetDate Datetime;
set @targetDate = '20190205 14:15:00';
Although I used a literal there, it could have been a string variable coming in that format.
Also, instead of convert(), you could use Try_Convert() with MS SQL 2008 and later.
And, I wonder why would you ever want to use string for a datetime. Use datetime and all would be painless.
ToShortDateString() I think is the .Net function for a DateTime value, what would be the need to make that conversion to string? If really needed, DateTime.ToString("yyyyMMdd HH:mm:ss") is the painless string version that is independent of date and language settings as sampled above.