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()
source to share
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.
source to share
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.
source to share