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?

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.