Find application password expiration date
Use the LOGINPROPERTY function by specifying the DaysUntilExpiration property:

select loginproperty('YourLoginName', 'DaysUntilExpiration');


Reference: BOL reference on LOGINPROPERTY