1

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?

souplex
  • 833
  • 1
  • 8
  • 10

1 Answers1

1

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.

Community
  • 1
Ian_H
  • 1,646
  • 10
  • 17
  • Was afraid of this answer. I do not see any alternative it either. – souplex Jan 20 '17 at 15:36
  • @souplex yep, I don't think there is an alternative I'm afraid (I did have a quick search but there's not really more to say I think it's pretty clear cut - I only added the irrelevant MySql bit after further looking). Who looks after the app, is there really no way it can be changed? – Ian_H Jan 20 '17 at 15:40