Post reply

SQL Server Version (T-SQL)

  • February 25, 2010 at 9:31 am

    #145520

    I need that this command returns if i'm using SQL Server 2000 or SQL Server 2005 or SQL Server 2008:

    select

    serverproperty ('productversion')

    I know that i need to do a CASE T-SQL, who can i do this?

    Thank you

  • February 25, 2010 at 9:33 am

    #1125063

    select @@VERSION

    Converting oxygen into carbon dioxide, since 1955.

  • February 25, 2010 at 9:36 am

    #1125068

    yap, that i know, but in this case what i was trying to achieve was making it return the version using a case , and if it returns 8, than it's 2000 if it returns 9 then it's 2005 if it returns 10 it's SQL Server 2008.

    My difficulty is in the case command.

    Can you help?

    Thank you

  • February 25, 2010 at 9:42 am

    #1125071

    What are you trying to do with the case statement?

    Converting oxygen into carbon dioxide, since 1955.

  • February 25, 2010 at 9:44 am

    #1125073

    i would use the PARSENAME function to return the 8/9/10:

    select serverproperty ('productversion') --9.00.4035.00

    select parsename(convert(varchar,serverproperty ('productversion')),4) --"9"

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 25, 2010 at 9:45 am

    #1125074

    when i do this command:

    select

    serverproperty ('productversion')

    if the first digit returned by the string is 8 then 2000

    if the first digit returned by the string is 9 then 2005

    ..........

  • February 25, 2010 at 9:49 am

    #1125077

    easy to grab what you need; here's a better example:

    select

    parsename(convert(varchar,serverproperty ('productversion')),4) As T4,

    parsename(convert(varchar,serverproperty ('productversion')),3) As T3,

    parsename(convert(varchar,serverproperty ('productversion')),2) As T2,

    parsename(convert(varchar,serverproperty ('productversion')),1) As T1

    --results:

    T4 T3 T2 T1

    -- --- ----- ---

    9 00 4035 00

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 25, 2010 at 9:50 am

    #1125079

    thank you lowell

  • February 25, 2010 at 9:52 am

    #1125080

    river1 (2/25/2010)

    when i do this command:

    select

    serverproperty ('productversion')

    if the first digit returned by the string is 8 then 2000

    if the first digit returned by the string is 9 then 2005

    ..........

    select case when cast(serverproperty ('productversion') as varchar(2)) like '8%' then '2000'

    when cast(serverproperty ('productversion') as varchar(2)) like '9%' then '2005'

    when cast(serverproperty ('productversion') as varchar(2)) like '10%' then '2008'

    end

    Converting oxygen into carbon dioxide, since 1955.

  • February 25, 2010 at 9:56 am

    #1125083

    Based on what Lowell started, how does this work for you?

    select case convert(tinyint,PARSENAME(convert(varchar(20),SERVERPROPERTY('productversion')),4))

    when 8 then 2000

    when 9 then 2005

    when 10 then 2008

    end

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 25, 2010 at 9:59 am

    #1125086

    that's perfect steve, thank you

  • February 25, 2010 at 11:51 am

    #1125151

    Steve and Wayne, nice solutions.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply