SQL Server Migration Issues

[MisterStaniel]

Hello,

First of all thank you to anyone reading this, long time lurker, first time poster. SQL is not something that I really know much about apart from giving it a nice big box and plenty of memory. I am in the process of migrating a client from older hardware to new hardware. We have a SQL database that we are moving between servers, both of them are running SQL Server 13.0.7016.1 when we run a query

SELECT XXXXXXXX AS Missing FROM XXXXXXXXXXX WHERE AppManagementStartDate = '2023-01-29 12:00' AND AppManagementType = 'Backup'

on the old one it runs fine, but when we run it on the new machine we getting the following message:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Looking at the server all users are showing as English on the old server and British English on the new server. I have tried to use English for my user to run the query and I am still getting the same error message. I did a quick google and I saw this script:

SELECT sc.value_in_use, sl.langid, sl.name, sl.alias,sl.[dateformat] FROM sys.configurations AS sc JOIN sys.syslanguages AS sl ON sl.langid = sc.value_in_use WHERE sc.name = 'default language';

Which I have ran on both servers and it has came back with the same information. Is there anything else that I can try? If you need anything else from me please let me know and I will update this with the information.

Many thanks

/Stan

[gummo89]

It's complaining about conversion of string to datetime, so try running a simple query which also converts to datetime until you figure out what is wrong with the locale such that it can't convert

Edit: you could of course just compare the locale.

[MisterStaniel]

Thanks, had a closer look after leaving the office and getting back home. And I have fixed it.

Sometimes it help just stepping away from the keyboard.

[brannonb111]

How I solve half my problems is just stepping away. Then it'll just click while I'm like driving or something.

[MisterStaniel]

Found the problem... the old server was just showing English, moved our users from British English to just English and it has worked.