A client migrated a db from SQL Server 2000 to 2012.
Their application runs into trouble afterwards. The cause is the following function invocation:
SELECT {fn curtime() }
Which returned date + time on SQL 2000 ('2017-01-20 07:24:26.997'), but only returns the time part ('07:24:26.997') on SQL 2012.
The compatibility mode cannot be set back further then 90 (SQL 2005).
Given that the application cannot be changed, is there any alternative solution for this issue?
The short answer is there is no way to fix this without changing the app.
That {fn curtime() } is and ODBC Scalar function that SQL Server does interpret, but really shouldn't be used natively.
Use GetDate() instead.
(I suppose you could use {fn curdate() } combined with {fn curtime() } )
I haven't got a SQL 2000 install anymore (that was 17 years ago!), so can't test what the {fn curtime() } was like then but I would've assumed that it only ever returned the time. Maybe not, but it doesn't make much difference now - you're a bit stuck.
The only option is to change the application. The justification shouldn't be too hard - an app that only works on SQL Server 2000 is not really acceptable, that has been out of support for a long while - it's a 17 year old system.
This earlier post is similar and the answer is a good one and gives some more information.
This is not relevant really but I'll add it (you can just ignore this bit):
curtime() is a builtin function in MySql. Your case is definitely ODBC syntax, but it's similar to the MySql one so thought I'd add it as trivia.
