日期转换问题MS对SQL Server的访问 [英] Date Conversion Issue MS Access to SQL Server

问题描述

我正在从一个存在的表A创建一个表B.在表A中，我有一列ValDate，它是varchar并包含Date.当我尝试创建表B时，如下所示，我在查询中使用了一个函数，但出现转换错误.表A也包含空值. MS Access:

I'm creating a table B from an exisitng table A. In the table A I have a column ValDate which is varchar and contains Date. When I try to create the table B I have a function used in the query as given below and I get a conversion error. Table A contains null values as well. MS Access:

((DateDiff("d",Date(),format(Replace(Replace([Table A].ValDate,".","/"),"00/00/0000","00:00:00"),"dd/mm/yyyy")))>0)).

表位于MS Access中，并正在迁移到SQL Server 2012.

Tables were in MS Access and are being migrated to SQL Server 2012.

SQL Server:

SQL Server:

((DATEDIFF(day,FORMAT( GETDATE(), 'dd-MM-yyyy', 'en-US' ),FORMAT( ValDate, 'dd-MM-yyyy', 'en-US' ))>0)) 

或

((DateDiff(day,GETDATE(),Format(Replace(Replace([TableA].[ValidFrom],'.','/'),'00/00/0000','00:00:00'),'dd/mm/yyyy')))

我尝试使用Convert，Format和Cast等几种方法转换日期，但最终出现如下错误.

I tried converting the date using several approachs like Convert , Format and Cast but I end up getting error as below.

Msg 8116, Level 16, State 1, Line 1 Argument data type date is invalid for argument 1 of isdate function.

Msg 8116, Level 16, State 1, Line 1 Argument data type date is invalid for argument 1 of isdate function.

我真的很感谢有人告诉我我在这里想念的东西.

I would really appreciate someone telling me what I'm missing here.

推荐答案

因为您在字符串字段中具有日期数据，所以很有可能您使用的某些值对您的预期日期格式无效.

since you have date data in a string field is very likely you have some value that is not valid against your expected date format.

将数据复制到sql server表中，然后执行检查和验证字符串字段的内容.

copy the data in a sql server table and then perform check and validation of the content of the string field.

看一下函数 try_convert 在检查包含日期值的字符串字段的内容时可能会有所帮助.

have a look to the function try_convert that can be helpful when checking the content of the string field containing the date values.

如果排除了不良数据，您可以再次应用公式(希望)得出不同的结果.
更好的解决方案是创建一个具有适当数据类型的单独字段，以存储从字符串字段转换的日期值，并将您的逻辑应用于该字段.

when bad data is ruled out you can apply again your formula with (hopefully) a different result.
a better solution would be to create a separate field with appropriate datatype to store date values converted from the string field and apply your logic to that field.

这篇关于日期转换问题MS对SQL Server的访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！