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, 2020 at 22:19
  • Thank you that indeed was the right trick and found the a related answer.
    – Ali
    Feb 5, 2020 at 22:27

1 Answer 1


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
          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'
print @SQLVersion

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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