Converting an nvarchar data type to a datetime data type resulted in a value out of range

Line of code 

INSERT [dbo].[Shop_Goods] ([TemplateID], [Name], [Remark], [CategoryID], [Description],
[Attack], [Defence], [Agility], [Luck], [Level], [Quality], [Pic], [MaxCount], [NeedSex],
[NeedLevel], [CanStrengthen], [CanCompose], [CanDrop], [CanEquip], [CanUse], [CanDelete],
[Script], [Data], [Colors], [Property1], [Property2], [Property3], [Property4], 
[Property5], [Property6], [Property7], [Property8], [Valid], [Count], [AddTime], 
[BindType], [FusionType], [FusionRate], [FusionNeedRate], [Hole], [RefineryLevel], 
[ReclaimValue], [ReclaimType], [CanRecycle], [FloorPrice], [SuitId], [CanTransfer],
[Price])
VALUES (4427, N'Ágata Roxa', N'null', 4, N'', 5, 5, 5, 5, 3, 4, N'eff127', 1, 2, 0, 0, 0, 
1, 1, 1, 1, N'', N'', N'', 0, 0, 0, 0, 0, 0, 0, 12, 0, 0,
CAST(N'2016-02-23 06:20:24.000' AS DateTime),2, 0, 0, 0, N'0,-1|0,-1|0,-1|0,-1|0,-1|0,-1',
 -1, 10, 1, 1, 100, 0, 1, NULL)

Message 242, Level 16, State 3, line 1922 the conversion of a type of nvarchar data in a datetime data type resulted in an out value from the break.

Wanted to know why the error, I'm running a query of almost 10 thousand lines and several gave this error.

Author: Maniero, 2016-07-21
Source

2 answers

Try using the DATEFORMAT , to set the date format.

SET DATEFORMAT ymd;  
SELECT CAST(N'2016-02-23 06:20:24.000' AS DateTime)

In your query would look like this; 

SET DATEFORMAT ymd; 
INSERT [dbo].[Shop_Goods] ([TemplateID], [Name], [Remark], [CategoryID], [Description],
[Attack], [Defence], [Agility], [Luck], [Level], [Quality], [Pic], [MaxCount], [NeedSex],
[NeedLevel], [CanStrengthen], [CanCompose], [CanDrop], [CanEquip], [CanUse], [CanDelete],
[Script], [Data], [Colors], [Property1], [Property2], [Property3], [Property4], 
[Property5], [Property6], [Property7], [Property8], [Valid], [Count], [AddTime], 
[BindType], [FusionType], [FusionRate], [FusionNeedRate], [Hole], [RefineryLevel], 
[ReclaimValue], [ReclaimType], [CanRecycle], [FloorPrice], [SuitId], [CanTransfer],
[Price])
VALUES (4427, N'Ágata Roxa', N'null', 4, N'', 5, 5, 5, 5, 3, 4, N'eff127', 1, 2, 0, 0, 0, 
1, 1, 1, 1, N'', N'', N'', 0, 0, 0, 0, 0, 0, 0, 12, 0, 0,
CAST(N'2016-02-23 06:20:24.000' AS DateTime),2, 0, 0, 0, N'0,-1|0,-1|0,-1|0,-1|0,-1|0,-1',
 -1, 10, 1, 1, 100, 0, 1, NULL)
 3
Author: Marco Souza, 2017-09-14 14:03:17

I had a similar problem, but with SELECT CAST(N'2016-02-23 06:20:24.000' AS DateTime) it did not work.

I also tested SELECT CONVERT(datetime, '2016-02-23 06:20:24.000') and it didn't work.

But, passing the 121 format, it worked: SELECT CONVERT(datetime, '2016-02-23 06:20:24.000', 121)

 1
Author: julianorinaldi, 2020-11-18 12:57:23