Allow newer SQL syntax for older versions?

I am maintaining an old ASP.net system that needs to run on older installations of SQL Server (since 2005).

Can I use features from later versions of SQL Server without errors Incorrect syntax near....

?

The current function I would like to use is AT DATE TIME

on Azure SQL 12 for example. select CONVERT(datetime, SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time')

to implement a custom function GETDATE()

, but earlier Sql versions refuse to compile it.

The only option doing exec()

in the sql string, or is there a more elegant solution?

--do a version check, and then run the appropriate sql string?
if (CHARINDEX('Azure', @@VERSION,0) > 0)    
    exec('select CONVERT(datetime, SYSDATETIMEOFFSET() AT TIME ZONE ''GMT Standard Time'')')
else
    select getDate()

      

+3


source to share


2 answers


Your choice looks like this:

  • Dynamic Sql that you do.
  • First ask the client's program / app to check the version and then edit your requests / requests (which actually just pushes the dynamic part towards the client).
  • Use stored procedures that are written specifically for the version of SQL Server in which they reside.


Of these, # 3 is the preferred solution.

+2


source


Here's an idea to work for your code ...

DECLARE @LocalOffset INT = -5
DECLARE @DaylightSavings INT = 0
DECLARE @OffSet INT = @LocalOffset + @DaylightSavings
SELECT DATEADD(HOUR,@OffSet,GETUTCDATE())

      



There are obviously other ways to achieve your goal. I would just populate a table or view with all timezones and then process your DST accordingly, even if that timezone even does so.

0


source







All Articles