Converting an nvarchar data type to a datetime data type resulted in a value outside the range

Navigation
3

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   of the range.

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

    
asked by anonymous 21.07.2016 / 17:00

1 answer

2

Try using DATEFORMAT to set the date format. 

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

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)
    
21.07.2016 / 19:56
Error or render screen in application pimefaces - NullPointException SQL query separated by space