Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Version (T-SQL) Expand / Collapse
Author
Message
Posted Thursday, February 25, 2010 9:31 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 752, Visits: 1,071
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
Post #872830
Posted Thursday, February 25, 2010 9:33 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
select @@VERSION

Converting oxygen into carbon dioxide, since 1955.

Post #872832
Posted Thursday, February 25, 2010 9:36 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 752, Visits: 1,071
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
Post #872837
Posted Thursday, February 25, 2010 9:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
What are you trying to do with the case statement?

Converting oxygen into carbon dioxide, since 1955.

Post #872841
Posted Thursday, February 25, 2010 9:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #872843
Posted Thursday, February 25, 2010 9:45 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 752, Visits: 1,071
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
..........
Post #872844
Posted Thursday, February 25, 2010 9:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #872847
Posted Thursday, February 25, 2010 9:50 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 752, Visits: 1,071
thank you lowell
Post #872849
Posted Thursday, February 25, 2010 9:52 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
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.

Post #872850
Posted Thursday, February 25, 2010 9:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 5,364, Visits: 8,952
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #872854
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse