SQL Server插入不同格式日期转标准格式的实现方法及支持格式

Great question! Let's tackle this head-on—working with date formats in SQL Server can be tricky, but there are clear ways to make sure your dates get stored correctly as 2018-05-15 16:09:21.

Can SQL Server auto-convert these formats? #

Short answer: It depends. SQL Server's ability to parse date strings automatically relies on your server/session's language and date format settings.

For example:

• If your server uses the default us_english language, it expects dates in mm-dd-yyyy order. So '05-15-2018 16:09:21' will parse correctly (May 15th), but '15-05-2018 16:09:21' will throw an error because 15 isn't a valid month.
• If you switch to a language like British (SET LANGUAGE British), SQL Server will expect dd-mm-yyyy order, so '15-05-2018' works, but '05-15-2018' fails.

Auto-conversion is inconsistent across environments, so relying on it isn't a safe practice. Instead, use explicit conversion or standard formats.

What date formats does SQL Server support? #

SQL Server recognizes a wide range of date formats, split into two main categories:

1. Language-agnostic (safe everywhere) #

The most reliable format is ISO 8601: 'YYYY-MM-DDTHH:MI:SS' (e.g., '2018-05-15T16:09:21'). This works regardless of your server's language settings—always use this if you can.

Other language-agnostic options include ODBC formats:

• Date only: {d '2018-05-15'}
• Time only: {t '16:09:21'}
• DateTime: {ts '2018-05-15 16:09:21'}

2. Language-dependent (varies by settings) #

These formats rely on your server's LANGUAGE or DATEFORMAT configuration:

• American: mm-dd-yyyy (style code 110)
• British/French: dd-mm-yyyy (style code 105)
• German: dd.mm.yyyy (style code 104)
• And many more—you can reference SQL Server's built-in documentation for the full list of style codes paired with CONVERT.

How to ensure correct conversion to 2018-05-15 16:09:21 #

Here are the best practices to make this work reliably:

Option 1: Use ISO 8601 for insertion #

If you can control the input format, send dates in ISO 8601 format. SQL Server will parse it correctly, and when you query the data, you can format it as yyyy-mm-dd hh:mi:ss using CONVERT:

-- Insert using ISO 8601
INSERT INTO YourTable (YourDateColumn)
VALUES ('2018-05-15T16:09:21');

-- Query and format as desired
SELECT CONVERT(varchar, YourDateColumn, 120) AS FormattedDate
FROM YourTable;

Style code 120 returns the exact yyyy-mm-dd hh:mi:ss format you want.

Option 2: Explicitly convert with TRY_CONVERT #

If you can't change the input format, use TRY_CONVERT (safer than CONVERT because it returns NULL instead of throwing errors for invalid dates) with the correct style code:

-- Convert 'dd-mm-yyyy hh:mi:ss' (style 105)
INSERT INTO YourTable (YourDateColumn)
VALUES (TRY_CONVERT(datetime2, '15-05-2018 16:09:21', 105));

-- Convert 'mm-dd-yyyy hh:mi:ss' (style 110)
INSERT INTO YourTable (YourDateColumn)
VALUES (TRY_CONVERT(datetime2, '05-15-2018 16:09:21', 110));

Using datetime2 instead of datetime is recommended—it's more precise and aligns with modern standards.

Option 3: Adjust session settings (not recommended for multi-language apps) #

If you're working in a single-language environment, you can set your session's date format to match your input:

-- Set session to expect dd-mm-yyyy order
SET DATEFORMAT dmy;

-- Now '15-05-2018' will parse correctly
INSERT INTO YourTable (YourDateColumn)
VALUES ('15-05-2018 16:09:21');

Just keep in mind this affects all date parsing in the session, so it's not ideal for apps that handle multiple date formats.

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