sql-server - 导出到 EXCEL 时 create_date/modify_date 不准确

Question

我运行查询来获取所有数据库和用户的列表。查询运行良好。

全选并将查询结果复制并粘贴到 Excel 中后，我可以正确显示多列。那么只有 create_date 和 modify_date 不正确。年月日不显示。见下图:

可以做什么？谢谢。

查询:

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT 
dbname,username ,logintype ,create_date ,modify_date ,
 STUFF(
(
 SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
 FROM @DB_USers user2
 WHERE
 user1.DBName=user2.DBName AND user1.UserName=user2.UserName
 FOR XML PATH('')
 )
 ,1,1,'') AS Permissions_user
 FROM @DB_USers user1
 GROUP BY
 dbname,username ,logintype ,create_date ,modify_date
 ORDER BY DBName,username

Answer 1

设置 Excel 中单元格的格式，以显示您想要显示日期和时间的单元格的日期和时间。

使用日期格式:

使用自定义格式: