Sql-server – Convert sql_variant to varchar to select left 2

sql servertype conversion

I am trying to populate a column (versionstatus) with if the service pack needs updating. I have created a lookup table which lists the SQL Version, and the latest product version, e.g.
SQLVersion LatestSP
SQL Server 2016 13.0.5026.0

I then have a second table which contains product versions, and want to compare this value with the LatestSP value, if the value in the first table is less than the second, then SP is not up to date.
I need to specify that where the product version in both tables begins with X (i.e. begins with 12 for SQL 2014, 13 for SQL 2016) then compare the numbers (so that I only compare the correct versions to each other) and if the value in Servers is less than the value in SPLookup, then populate the versionstatus column with 'NEEDS UPDATING'.
However I am having issues converting sql_variant to varchar to be able to select the first 2 digits.

Here is what I have so far, and would extrapolate this to the other versions.
productversion in Servers table is sql_variant, LatestSP in SPLookup is varchar.

 INSERT INTO Servers 
 (versionstatus)
 SELECT CASE WHEN LEFT(X.productversion, 2) = '12' AND LEFT(Y.LatestSP, 2) = '12' AND X.productversion < Y.LatestSP THEN 'UPDATE NEEDED' 
            WHEN LEFT(X.productversion, 2) = '12' AND LEFT(Y.LatestSP, 2) = '12' AND X.productversion >= Y.LatestSP THEN 'UP TO DATE'
        END AS versionstatus
        FROM Servers X INNER JOIN SPLookup Y ON X.productversion = Y.LatestSP

Best Answer

Try explicitly converting the sql_variant column to varchar before applying the LEFT function. The following simple example worked for me.

Declare @V sql_variant
set @v = '13.0.5026.0'
select left(convert(varchar(max),@v),2)

| (No column name) |
|------------------|
| 13               |

Related Solutions

SQL Server – Fixing Varchar to Datetime Conversion Out-of-Range Error

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format.

Try this:

select count(*) 
  from dbo.profile 
  where [created] between convert(datetime,'2014-11-01T00:00:00.000') 
                      and convert(datetime,'2014-11-30T23:59:59.997');

Querying using dates and/or datetimes can be tricky, to make sure you are getting what you are looking for I recommend reading:

Aaron Bertrand's Bad habits to kick : mis-handling date / range queries
Robyn Page's SQL Server DATE/TIME Workbench

edit: to clarify the out of range value in your error message would be from interpreting the month as 30 and the day as 11.

Sql-server – Remove Redundant Data

You can convert the version string to XML and then extract the four parts replacing no value (nulls) with a 0. Put the version back together and compare to your original table.

-- Sample table
declare @T table
(
  Title varchar(20),
  Version varchar(10)
);

-- Sample data
insert into @T(Title, Version) values
('SQL Server', '13'),
('SQL Server', '13.0.0'),
('SQL Server', '13.0.0.0'),
('SQL Server', '13.0.0.175'),
('Visual Studio', '13'),
('Visual Studio', '13.0.0'),
('Visual Studio', '13.0.0.0'),
('Visual Studio', '13.0.0.175');

-- Get the required versions
select *
from @T as T1
where exists (
             select *
             from @T as T2
               -- Convert version to XML
               cross apply (select cast(replace(T2.Version, '.', '<X/>') as xml)) as X(Value)

               -- Extract the values from the XML and build a new version string with 0 instead of null.
               cross apply (select X.Value.value('text()[1]', 'varchar(10)')+'.'+
                                     isnull(X.Value.value('text()[2]', 'varchar(10)'), '0')+'.'+
                                     isnull(X.Value.value('text()[3]', 'varchar(10)'), '0')+'.'+
                                     isnull(X.Value.value('text()[4]', 'varchar(10)'), '0')) as V(Version)
             where  T1.Title = T2.Title and
                    T1.Version = V.Version
             );

-- Or if you want to delete from your original table ...
delete from @T
where not exists (
                 select *
                 from @T as T2
                   -- Convert version to XML
                   cross apply (select cast(replace(T2.Version, '.', '<X/>') as xml)) as X(Value)

                   -- Extract the values from the XML and build a new version string with 0 instead of null.
                   cross apply (select X.Value.value('text()[1]', 'varchar(10)')+'.'+
                                         isnull(X.Value.value('text()[2]', 'varchar(10)'), '0')+'.'+
                                         isnull(X.Value.value('text()[3]', 'varchar(10)'), '0')+'.'+
                                         isnull(X.Value.value('text()[4]', 'varchar(10)'), '0')) as V(Version)
                 where  [@T].Title = T2.Title and
                        [@T].Version = V.Version
                 );

select *
from @T;

Related Question