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

• @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

2 Answers 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

fiddle