如何解决日期时间转换错误 [英] How to solve datetime conversion errors
问题描述
我想在数据库中将nvarchar(250)转换为datetime。
实际上我已经将访问数据库转换为sql,在访问中他们提到了INV
日期数据类型为文本,因此它被转换为nvarchar(250)所以我需要
转换为datetime。
它在sql中保存就像这种格式02-01-2015
我写的这个代码在sql
更新soINVOICE设置INV_DATE = convert(datetime,convert(datetime,INV_DATE,(105)))
但是我一直都会收到这个错误
nvarchar数据类型转换为日期时间数据类型导致了一个不合适的数据类型范围值。
所以请解决此错误
I want to convert a nvarchar(250) to datetime in a database.
Actually I have converted access database into sql in access they mention INV
Date datatype as text so it is converted into nvarchar(250) so that I need to
convert that in datetime.
It is saving in sql like this format 02-01-2015
I have written this code in sql
update soINVOICE set INV_DATE =convert(datetime,convert(datetime,INV_DATE ,(105)))
but I am getting this error all the time
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
So Please Resolve this error
推荐答案
我会在高级别中执行此操作语言:C#或类似的,并在那里而不是在SQL中进行转换 - 您正在使用基于文本的日期,而且最有可能因为它给你带来问题的原因是存储的日期并非全部有效 - 这是始终存储为DATETIME值的原因之一。
如果你这样做的话通用语言,您可以更好地支持错误,并可以记录或手动更正无效条目。在SQL中执行此操作要困难得多!
I'd do it in a high level language: C# or similar, and do the conversion there rather than in SQL - you are working with text based dates, and the most likely reason it's giving you problems is that the dates stored are not all valid - that's one of the reasons for always storing then as DATETIME values.
If you do this in a general purpose language, you get a lot better support for errors, and can log or manually correct invalid entries. It's a heck of a lot harder to do that in SQL!
update soINVOICE set INV_DATE =convert(datetime,convert(varcahr(16),INV_DATE ,(105)))
DateTime.TryParse
[ ^ ]和DateTime.ParseExac
t [ ^ ]可以帮到你解决前端的一些错误。
DateTime.TryParse
[^] andDateTime.ParseExac
t[^] can help you resolve some of these errors at the front end.
这篇关于如何解决日期时间转换错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!