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:

2008

or

2005

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

Do anyone knows such a query?

Thanks!

Answer 1

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

Answer 2

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);