t sql - '2025-03-11 12:07:32.897475' fails to be converted to Datetime in SQL Server - Stack Overflow

admin管理员组

文章数量:1346662

A string with more than 3 decimals fails converting to a datetime.

For example:

DECLARE @sample TABLE
(
    [Id] [UNIQUEIDENTIFIER] NOT NULL,
    [AccountId] [UNIQUEIDENTIFIER] NOT NULL,
    [CreatedById] [UNIQUEIDENTIFIER] NOT NULL,
    [CreatedAt] [DATETIME] NOT NULL,
    [StartTime] [DATETIME] NULL,
    [EndTime] [DATETIME] NULL,
    [ProcessorTimeUsed] [INT] NOT NULL,
    [FileName] [NVARCHAR](255) NOT NULL,
    [PlanProgress] [NVARCHAR](MAX) NOT NULL,
    [Succeeded] [BIT] NULL
);

INSERT INTO @sample
(
    Id,
    AccountId,
    CreatedById,
    CreatedAt,
    StartTime,
    EndTime,
    ProcessorTimeUsed,
    FileName,
    PlanProgress,
    Succeeded
)
VALUES
(
 '269912c1-9197-4602-9914-7dd273f183b8', 
 '8ee8eead-61fb-4962-b411-887f97157904',
 '17f0e8ef-19c0-450b-ac5d-b1f10156ad6e', 
 '2025-03-11 12:07:32.897475', 
 '2025-03-11 12:07:32.907540',
 '2025-03-11 12:18:05.006104', 102, 
 '0083cce1-f30d-4ab7-9a68-8409ccc0f372.db', '', 1);

This fails with the error:

Msg 241, Level 16, State 1, Line 33
Conversion failed when converting date and/or time from character string.

If you shorten part after seconds to only milliseconds, it works.

Is there a setting or something that would make it possible to work without removing extra digits from the text?

It would be easier if there is, than to use workarounds such as using a temp table with those columns as datetimeoffsets.

A string with more than 3 decimals fails converting to a datetime.

For example:

DECLARE @sample TABLE
(
    [Id] [UNIQUEIDENTIFIER] NOT NULL,
    [AccountId] [UNIQUEIDENTIFIER] NOT NULL,
    [CreatedById] [UNIQUEIDENTIFIER] NOT NULL,
    [CreatedAt] [DATETIME] NOT NULL,
    [StartTime] [DATETIME] NULL,
    [EndTime] [DATETIME] NULL,
    [ProcessorTimeUsed] [INT] NOT NULL,
    [FileName] [NVARCHAR](255) NOT NULL,
    [PlanProgress] [NVARCHAR](MAX) NOT NULL,
    [Succeeded] [BIT] NULL
);

INSERT INTO @sample
(
    Id,
    AccountId,
    CreatedById,
    CreatedAt,
    StartTime,
    EndTime,
    ProcessorTimeUsed,
    FileName,
    PlanProgress,
    Succeeded
)
VALUES
(
 '269912c1-9197-4602-9914-7dd273f183b8', 
 '8ee8eead-61fb-4962-b411-887f97157904',
 '17f0e8ef-19c0-450b-ac5d-b1f10156ad6e', 
 '2025-03-11 12:07:32.897475', 
 '2025-03-11 12:07:32.907540',
 '2025-03-11 12:18:05.006104', 102, 
 '0083cce1-f30d-4ab7-9a68-8409ccc0f372.db', '', 1);

This fails with the error:

Msg 241, Level 16, State 1, Line 33
Conversion failed when converting date and/or time from character string.

If you shorten part after seconds to only milliseconds, it works.

Is there a setting or something that would make it possible to work without removing extra digits from the text?

It would be easier if there is, than to use workarounds such as using a temp table with those columns as datetimeoffsets.

• sql-server
• t-sql

Share Improve this question Follow edited 2 days ago marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked 2 days ago Cetin BasozCetin Basoz 23.9k33 gold badges3333 silver badges4747 bronze badges 11

• @BartMcEndree, as I said, I could use workarounds with temp table, making those fields other type like datetimeoffsett. The question is how would I do without a temp table in between? I really find it ridiculous SQL server fails making this conversion. – Cetin Basoz Commented 2 days ago
• @BartMcEndree, I repeat again, I don't have control over the actual data type and\or no intention to change underlying data type. – Cetin Basoz Commented 2 days ago
• @BartMcEndree, can you tell me why wouldn't say '2025-01-01 10:00:00.000000' fail converting to a datetime but '2025-01-01 10:00:00.000' succeed. They are the same thing, no? Looks like I have to use a temp table :( – Cetin Basoz Commented 2 days ago
• The first would fail, not wouldn't , @Cetin, and it would fail because it have too much precision; they they represent the same value is irrelevant.datetime does not recognise values with a precision grater than 3; one reason why the newer date and time data types are recommended. – Thom A Commented 2 days ago
• 1 It's behaving as it always has with datetime since it was added to Sybase way back in the 80's, @CetinBasoz . With respect, the weakness if your data type choice; if you need 4+ digits for precision, use a data type that supports that precision. – Thom A Commented 2 days ago

 |  Show 6 more comments

2 Answers 2

Sorted by: Reset to default 2

You can convert to datetime2 with style 121 first, then to datetime.

INSERT INTO @sample
(
    Id,
    AccountId,
    CreatedById,
    CreatedAt,
    StartTime,
    EndTime,
    ProcessorTimeUsed,
    FileName,
    PlanProgress,
    Succeeded
)
VALUES
('269912c1-9197-4602-9914-7dd273f183b8', '8ee8eead-61fb-4962-b411-887f97157904', '17f0e8ef-19c0-450b-ac5d-b1f10156ad6e',
 CONVERT(datetime, CONVERT(datetime2, '2025-03-11 12:07:32.897475', 121)),
 CONVERT(datetime, CONVERT(datetime2, '2025-03-11 12:07:32.907540', 121)),
 CONVERT(datetime, CONVERT(datetime2, '2025-03-11 12:18:05.006104', 121)),
 102, '0083cce1-f30d-4ab7-9a68-8409ccc0f372.db', '', 1);

db<>fiddle

Really you should use properly parameterized queries directly from your application, rather than creating raw queries, then you wouldn't have such issues.

Although you have indicated quite confidently that you cannot change the text of the request, I suggest you look at another option.

Leaving the entire fragment VALUES(...) unchanged.
Perhaps such a change is easier than importing into a temporary table and transferring to the target table.

INSERT INTO @sample
(
    Id,
    AccountId,
    CreatedById,
    CreatedAt,
    StartTime,
    EndTime,
    ProcessorTimeUsed,
    FileName,
    PlanProgress,
    Succeeded
)
-- added text 1
select Id,AccountId,CreatedById,
    cast(CreatedAt as datetime2) CreatedAt,
    cast(StartTime as datetime2) StartTime,
    cast(EndTime as datetime2) EndTime,
    ProcessorTimeUsed,FileName,PlanProgress,Succeeded 
from (
-- 1
  VALUES
('269912c1-9197-4602-9914-7dd273f183b8', '8ee8eead-61fb-4962-b411-887f97157904',
 '17f0e8ef-19c0-450b-ac5d-b1f10156ad6e', '2025-03-11 12:07:32.897475'  , '2025-03-11 12:07:32.907540',
 '2025-03-11 12:18:05.006104', 102, '0083cce1-f30d-4ab7-9a68-8409ccc0f372.db', '', 1)
-- added text 2
 )t(
    Id,AccountId,CreatedById,CreatedAt,StartTime,EndTime,ProcessorTimeUsed,
    FileName,PlanProgress,Succeeded
);
-- 2

Id	AccountId	CreatedById	CreatedAt	StartTime	EndTime	ProcessorTimeUsed	FileName	PlanProgress	Succeeded
269912c1-9197-4602-9914-7dd273f183b8	8ee8eead-61fb-4962-b411-887f97157904	17f0e8ef-19c0-450b-ac5d-b1f10156ad6e	2025-03-11 12:07:32.897	2025-03-11 12:07:32.907	2025-03-11 12:18:05.007	102	0083cce1-f30d-4ab7-9a68-8409ccc0f372.db		True

fiddle

本文标签： t sql3920250311 12073289747539 fails to be converted to Datetime in SQL ServerStack Overflow