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