Sql-server – Extract Year only from ‘Select @@version’ output

sql servert-sql

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.

Best Answer

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
Related Question