SELECT语句中SQL_Variant数据类型来源疑问——LOGINPROPERTY相关

Great question! Let's clear up this confusion step by step.

First, the core thing to grasp: while Microsoft's documentation notes that the actual value returned for the 'PASSWORDLASTSETTIME' attribute is a DATETIME type, the LOGINPROPERTY function itself has a formal return type of sql_variant. This is intentional—because the function needs to support multiple attributes that return different data types:

• Attributes like 'PASSWORDLASTSETTIME' or 'BADPASSWORDLASTSETTIME' return datetime values
• Attributes like 'ISLOCKED' or 'ISDISABLED' return bit values
• Attributes like 'PASSWORDHASH' return varbinary values

To accommodate all these varied return types, SQL Server uses sql_variant as the function's base return type. The documentation refers to the underlying data type of the specific attribute's value, not the function's overall return type.

How to Confirm This #

You can verify the function's return type using the sys.dm_exec_describe_first_result_set dynamic management view:

SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT LOGINPROPERTY(''sa'', ''PASSWORDLASTSETTIME'') AS LastChangeTime',
    NULL,
    0
);

Running this will return sql_variant as the system type name, even though the value stored inside is a datetime.

Fixing the sql_variant Issue #

If you need a native DATETIME column instead of sql_variant, just explicitly cast the result:

SELECT 
    [name],
    CAST(LOGINPROPERTY([name], 'PASSWORDLASTSETTIME') AS DATETIME) AS LastChangeTime
FROM sys.sql_logins;

This converts the sql_variant-wrapped datetime value into a proper DATETIME type, eliminating the sql_variant-related warning or issue you're encountering.

内容的提问来源于stack exchange，提问作者user1910240