SQL插入日期时间报错解决：字符串转日期失败及分两行插入方法

Hey there, let's work through this SQL date conversion issue and your insertion requirement together.

First, let's break down why you're getting that Conversion failed when converting date and/or time from character string error: SQL Server's ability to parse date strings depends on your session's language/format settings. The '04/07/2018 18:18:29' format can be ambiguous (is it MM/DD/YYYY or DD/MM/YYYY?), which leads to parsing failures when the setting doesn't match the actual date structure.

Step 1: Fix the Date Conversion Error #

To resolve the parsing issue, you have two reliable options:

Option 1: Use CONVERT with an explicit style code

Specify exactly what format your string uses with a style code. For example:

• If your string is DD/MM/YYYY HH:MI:SS (4th July 2018), use style 103:

  declare @Sampledate table (SampleDate datetime)
  insert into @Sampledate 
  select CONVERT(datetime, '04/07/2018 18:18:29', 103)
  

• If it's MM/DD/YYYY HH:MI:SS (7th April 2018), use style 101:

  declare @Sampledate table (SampleDate datetime)
  insert into @Sampledate 
  select CONVERT(datetime, '04/07/2018 18:18:29', 101)
  

Option 2: Use ISO 8601 format (most reliable)

Adjust your browser to send the date in ISO 8601 format (YYYY-MM-DDTHH:MM:SS), which SQL Server parses correctly regardless of language settings:

declare @Sampledate table (SampleDate datetime)
insert into @Sampledate 
select '2018-07-04T18:18:29'

Step 2: Insert Date & Time as Separate Rows #

To insert the date component (with time set to 00:00:00) and the time component (with date set to the default 1900-01-01) as two separate rows, use this approach:

declare @Sampledate table (SampleDate datetime)
declare @OriginalDateTimeStr varchar(20) = '04/07/2018 18:18:29'

-- First convert the string to a datetime to avoid repeated parsing
declare @OriginalDateTime datetime = CONVERT(datetime, @OriginalDateTimeStr, 103) -- Use your correct style code here

-- Insert just the date (time defaults to 00:00:00)
insert into @Sampledate
select CAST(@OriginalDateTime AS date)

-- Insert just the time (date defaults to 1900-01-01)
insert into @Sampledate
select CAST(@OriginalDateTime AS time)

-- Verify the results
select * from @Sampledate

After running this, your @Sampledate table will have two rows:

• 2018-07-04 00:00:00.000 (pure date)
• 1900-01-01 18:18:29.000 (pure time)

If your browser already sends the date and time as separate strings (e.g., '04/07/2018' and '18:18:29'), you can simplify the code to:

declare @Sampledate table (SampleDate datetime)
insert into @Sampledate select CONVERT(datetime, '04/07/2018', 103) -- Pure date
insert into @Sampledate select CONVERT(datetime, '18:18:29') -- Pure time

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