SQL Server 2017存储过程手动执行正常，作业执行日期转换报错

Hey there, let's dig into this frustrating issue where your stored procedure works fine in SSMS but bombs out when run via a SQL Server Agent job. I’ve debugged this exact scenario dozens of times, and the root cause is almost always differences in regional/locale settings between your interactive SSMS session and the account running the SQL Server Agent service.

Why This Happens #

When you run the stored procedure manually in SSMS, it uses your user account’s regional settings to format the date string returned by xp_cmdshell. But SQL Server Agent runs under its own service account—this account might have a different date format (e.g., your session uses MM/DD/YYYY, but the agent uses DD/MM/YYYY). When your code tries to convert that mismatched string to a DATE type, it throws the 22007 conversion error.

Plus, the dir command used in xp_cmdshell outputs dates based on the running account’s locale, so the string format can shift depending on who’s executing it.

Step-by-Step Fixes #

1. First, Identify the Exact Date Format the Job Sees #

Before fixing, you need to know what date string the agent is getting. Modify your stored procedure to log the raw output from xp_cmdshell to a permanent table (temp tables won’t persist after the job runs):

-- Create a permanent log table (run this once)
CREATE TABLE CmdOutputLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    RawOutput VARCHAR(255),
    RunTime DATETIME DEFAULT GETDATE()
);

-- Update your stored procedure to insert raw output
INSERT INTO CmdOutputLog (RawOutput)
EXEC xp_cmdshell 'dir "C:\Your\Target\File.txt" /tw'; -- /tw = show last write (modified) date

-- After running the job, query this table to see the date format
SELECT * FROM CmdOutputLog WHERE RawOutput LIKE '[0-9]%'; -- Filter to date-containing rows

Run the job, then check the log table. You’ll see exactly what date string the agent is trying to convert.

2. Use Explicit Date Conversion with Style Codes #

Instead of relying on SQL Server’s default conversion, use CONVERT with a style code that matches the format you saw in the log. For example:

• If the string is MM/DD/YYYY: CONVERT(DATE, YourDateColumn, 101)
• If it’s DD/MM/YYYY: CONVERT(DATE, YourDateColumn, 103)
• If it’s YYYY-MM-DD: CONVERT(DATE, YourDateColumn, 23)

Make sure to also trim any extra whitespace or characters from the date string first. For example:

UPDATE #TempTable
SET DateColumn = CONVERT(DATE, LTRIM(RTRIM(SUBSTRING(DateString, 1, 10))), 101)
WHERE DateString LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%';

3. (Better) Use PowerShell for Consistent Date Formatting #

The most reliable fix is to bypass dir entirely and use PowerShell to output a fixed-format date, which doesn’t depend on regional settings. Replace your xp_cmdshell call with this:

EXEC xp_cmdshell 'powershell -Command "(Get-Item ''C:\Your\Target\File.txt'').LastWriteTime.ToString(''yyyy-MM-dd'')"';

This will always return a YYYY-MM-DD string, which converts cleanly to a DATE type without any style code needed. Just make sure to filter out any null or empty rows from the output.

4. Verify the Agent Service Account’s Regional Settings #

If you want to align settings, you can log into the SQL Server using the agent’s service account, open Command Prompt, run dir, and check the date format. You can adjust the account’s regional settings via Control Panel, but this might affect other services running under that account—so proceed with caution.

Final Notes #

Always test your fixes in the job context, not just SSMS. The key here is eliminating ambiguity in the date string format, either by explicitly converting with the right style or forcing a consistent output from the command line tool.

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