首先，咱们得先调整你的TMP表结构——原来的表只有文件名列，现在要新增日期时间列才能存储对应的时间信息。分两种情况处理：

1. 调整表结构 #

如果还没创建TMP表，直接建包含两列的表：

CREATE TABLE TMP (
    FILENAME VARCHAR(100),
    FileDateTime DATETIME  -- 用来存储文件的修改/创建/访问时间，按需选择
);

如果已经创建了TMP表，就用ALTER语句新增列：

ALTER TABLE TMP ADD FileDateTime DATETIME;

2. 通过xp_cmdshell获取带日期时间的目录输出 #

直接用dir /b只能拿到纯文件名，咱们需要用完整的dir命令输出包含日期时间的内容，再通过临时表解析提取信息：

-- 先创建临时表存储dir的原始输出内容
CREATE TABLE #DirOutput (
    OutputLine VARCHAR(255)
);

-- 执行dir命令：/tw表示获取文件修改时间（可换成/tc=创建时间，/ta=访问时间）
INSERT INTO #DirOutput
EXEC xp_cmdshell 'dir "S:\Common\Backups\*.bak" /tw';

-- 解析输出内容，提取日期时间和文件名，插入到TMP表
INSERT INTO TMP (FILENAME, FileDateTime)
SELECT 
    -- 提取文件名：前19位是日期时间，从第20位开始截取文件名
    LTRIM(RIGHT(OutputLine, LEN(OutputLine) - 19)) AS FILENAME,
    -- 转换日期时间：前19位是"MM/DD/YYYY HH:MM AM/PM"格式，转成DATETIME类型
    CONVERT(DATETIME, LEFT(OutputLine, 19), 101) AS FileDateTime
FROM #DirOutput
WHERE 
    OutputLine IS NOT NULL  -- 过滤空行
    AND ISDATE(LEFT(OutputLine, 19)) = 1;  -- 只保留包含有效日期的行（过滤表头、总计行）

-- 用完临时表就删掉
DROP TABLE #DirOutput;

3. 你之前出错的大概率原因 #

• 没调整表结构，直接往单列表里插入两列数据，导致列数不匹配报错
• 依然用了/b参数的dir命令，输出里没有日期时间数据，自然无法提取对应字段

另外要注意：

• xp_cmdshell需要SQL Server管理员开启（执行sp_configure 'xp_cmdshell', 1; RECONFIGURE;即可）
• 执行SQL的账户必须有访问S:\Common\Backups文件夹的权限，否则dir命令会返回权限错误或空结果

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