SQL query to get sql year version

Question

I'm using SQL Server and I need a SQL query which returns the year of the version.

I'm familiar with the next methods:

select @@version

select SERVERPROPERTY('productversion')

but I need a query that will return for example:

or

The first query I mentioned requires dirty parsing and the second returns some product number.

Do anyone knows such a query?

Thanks!

I think it will end in something like Select Cast(SubString(@@version,CharIndex('2',@@Version),4) as int) — bummi, Nov 11 '12 at 14:03
@bummi, thanks! but I don't know if it safe enough to parse it that way. I need to verify with all possible @@version result that the first '2' digit is part if the year. — elirandav, Nov 11 '12 at 15:59

score 11 · Accepted Answer · edited Nov 28 '19 at 09:12

Try this - it just inspects the first two characters of the version number and returns the corresponding "year" for the SQL Server version.

SELECT
    'SQL Server Year ' + 
    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

Of course, you can package this up into a user-defined function to make it "prettier"

Source: MSDN

score 1 · Answer 2 · answered Nov 11 '12 at 14:05

1

Since the first number of the property SERVERPROPERTY('productversion') is the major version number:

major.minor.build.revision

Then you can create a temp table containing each edition major version number and its year. Then you will end up with the folowing query:

 DECLARE @productversion VARCHAR(100);
 SELECT @productversion = CAST(SERVERPROPERTY('productversion') AS VARCHAR(100));
SELECT years.year, years.name
FROM 
(
  VALUES(9, '2005', 'SQL Server 2005'), 
        (10, '2008', 'SQL Server 2008 OR SQL Server 2008 R2'),
        ... 
) AS years(majornumber, [year], name)
WHERE years.majornumber = LEFT(@productversion, 
                               CHARINDEX('.', @productversion , 1) - 1);

answered Nov 11 '12 at 14:05

Mahmoud Gamal

78,257
17
139
164

Also: 8 = SQL Server 2000, 11 = SQL Server 2012 – marc_s Nov 11 '12 at 15:14
Thanks! I will use it. Where did you find the match between the major and year version? – elirandav Nov 11 '12 at 15:43
@kernelMode - I googled it. Here is a page for example [**SQL Server Versions**](http://social.technet.microsoft.com/wiki/contents/articles/783.sql-server-versions.aspx). But note that you are looking for the first two numbers, the major. – Mahmoud Gamal Nov 11 '12 at 15:49

score 0 · Answer 3 · answered Apr 21 '16 at 12:19

SELECT 'SQL Server ' + 
    CASE CAST(SERVERPROPERTY('productversion') AS CHAR(2))
          WHEN '8.' THEN '2000'
          WHEN '9.' THEN '2005'
          WHEN '10' THEN '2008/2008R2'
          WHEN '11' THEN '2012'
          WHEN '12' THEN '2014'
          WHEN '13' THEN '2016'
    END

Output -

----------------------
SQL Server 2016

SQL query to get sql year version

3 Answers3