2

I'm trying to automate a short task in configuring our SQL Server for which I have to grab SQL Server year. Is there a way to extract year only from the output of following query: Select @@version ?

As you know it outputs the whole informatioin e.g.: Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64) Nov 16 2019 01:14:50 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

I tried the year function as below but I'm getting conversion error.

declare @SQLVersion varchar(100)
set @SQLVersion = (Select year(@@version))
print @SQLVersion

Or is there another way of grabbing the year only using tsql?

Any help is appreciated.

  • 4
    In the output of version there are 4 possible years so parsing it for year can be problematic. Can you use product version instead of year: SELECT @SQLVersion serverproperty('ProductMajorVersion') . 15= 2019, 14=2017, 13= 2016 – Bob Klimes Feb 5 at 22:19
  • Thank you that indeed was the right trick and found the a related answer. – Ali Feb 5 at 22:27
3

Found a related answer from this post: https://stackoverflow.com/questions/13331621/sql-query-to-get-sql-year-version. Below code will provide year only. Credit goes to that post.

Declare @SQLVersion varchar(100)
Declare @TargetServer Varchar (4)
--Extract SQL Server Year 
set @SQLVersion = (SELECT
   CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 2)
          WHEN '8.' THEN '2000'
          WHEN '9.' THEN '2005'
          WHEN '10' THEN '2008'
          WHEN '11' THEN '2012'
          WHEN '12' THEN '2014'
          WHEN '13' THEN '2016'
          WHEN '14' THEN '2017'
          WHEN '15' THEN '2019'
    END)
print @SQLVersion
| improve this answer | |

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.