CODEWITHSUNDEEP
sqlsql-serverversion
elirandav
elirandav

Reputation: 2073

SQL query to get sql year version

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!

Upvotes: 3

Views: 7244

Answers (3)

marc_s
marc_s

Reputation: 755321

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

Upvotes: 11

Devart
Devart

Reputation: 122032

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

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

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

Upvotes: 1

Related Questions