>Is there a setting in SSMS which controls the date format? I have a script which used to work for years but on a client site, it doesn't has it says that this creates a value which is off limit. This seems to be related to the INSERT line of the script which is built from a specific date format.
SSMS doesn't have any specific format, but if you want to make sure you always pass valid dates, use ISO date format, e.g.
' YYYYMMDD'
Check CAST & CONVERT topic in BOL
and also
The ultimate guide to the datetime datatypes
If it's not broken, fix it until it is.
My Blog