Where does SQL_Variant datatype come from in select statement using LOGINPROPERTY

Posted on

Question :

In the below code I do not understand where SQL_Variant is coming from – The column it is complaining about is LOGINPROPERTY([name], 'PASSWORDLASTSETTIME') AS LastChangeTime According to MSFT documents this is a DATETIME column.

Where does the SQL_Variant datatype come from?

DROP TABLE #PasswordChange;
CREATE TABLE #PasswordChange (name VARCHAR(50),
lastPWChangeTS DATETIME,
PWhash VARBINARY(26));

INSERT INTO #PasswordChange (name,
lastPWChangeTS,
PWhash)
SELECT [name],
LOGINPROPERTY([name], 'PASSWORDLASTSETTIME') AS LastChangeTime,
LOGINPROPERTY([name], 'PASSWORDHASH') AS PWhash
FROM sys.syslogins
WHERE loginproperty([name], 'PASSWORDLASTSETTIME') > DATEADD(HOUR, -24, 
GETDATE());

Msg 257, Level 16, State 3, Line 6
Implicit conversion from data type sql_variant to datetime is not 
allowed. Use the CONVERT function to run this query.

Answer :

The return value depends on the input value, but the result set returns sql_variant to accommodate all possible return types.

You’ll have to make some adjustments to your code.

INSERT INTO #PasswordChange
     ( name, lastPWChangeTS, PWhash )
SELECT  name,
        CONVERT(DATETIME, ISNULL(loginproperty([name], 'PASSWORDLASTSETTIME'), 0)) AS LastChangeTime,
        CONVERT(VARBINARY(26), LOGINPROPERTY(name, 'PASSWORDHASH')) AS PWhash
FROM    sys.syslogins
WHERE   CONVERT(DATETIME, loginproperty([name], 'PASSWORDLASTSETTIME')) > DATEADD(HOUR, -24, GETDATE());

Leave a Reply

Your email address will not be published. Required fields are marked *