Conversion of an nvarchar data type into a datetime data type resulted in a value outside the range

Asked

Viewed 15,372 times

5

Code line

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 data type datetime resulted in an off value of halftime.

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

sql-server query typing type-conversion

  • Your mistake already says it all, you have an Insert that is with the wrong date. CAST(N'2016-02-23 06:20:24.000' AS DateTime), Where’s your 1000 coming from? It’s a select?

  • Or try using the Convert(varchar,CAST,103).

2 answers

4


Try to use 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)
  • 1

    Thanks friend worked :)

3

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

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

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

Browser other questions tagged sql-server query typing type-conversion

You are not signed in. Login or sign up in order to post.