I had this requirement to create some dynamic SQL script for a client. I had a data source with dates in UK format. The script had to have dates in datetime’s normal format. I was creating the script from the data source. Lets look at an example:
I had a varchar value ‘18/10/2012’ in the data source. I had to put equivalent datetime value in the database. So the dynamic script was required to have correct value.
Using CONVERT (DATETIME, value, 103) gave me the correct datetime format but using that for dynamic SQL was not possible as the returned result was DATETIME.
So I had to again CONVERT the DATETIME into VARCHAR with option 121. Here is an example: