vb.net 使用 GETDATE() 将数据类型 varchar 转换为 datetime 时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16518150/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error converting data type varchar to datetime using GETDATE()
提问by rxs179
I realise this is quite a common problem banded around forums, but I am at a loss as to what I've done wrong here.
我意识到这是一个在论坛上很常见的问题，但我不知道我在这里做错了什么。
I have a 'Current' table something like:
我有一个“当前”表，如下所示：
ID(uniqueidentifier)
Name(nvarchar max)
FileName(nvarchar max)
FileVersion(smallint)
CurrentVersionDate(datetime)
The CurrentVersionDate is entered using the GETDATE() function within SQLserver.
CurrentVersionDate 是使用 SQLserver 中的 GETDATE() 函数输入的。
When a user hit's 'Create file' on my website, the details of this table are transferred to a 'History' Table. So I use a stored procedure to select * details from 'Current' and save each value as a variable (i'm using vb.net in a visual studio project). I'll show the date variable as this is the problem. I'm saving this as a 'date' variable:
当用户在我的网站上点击“创建文件”时，该表的详细信息将转移到“历史”表中。因此，我使用存储过程从“当前”中选择 * 详细信息并将每个值保存为变量（我在 Visual Studio 项目中使用 vb.net）。我将显示日期变量，因为这是问题所在。我将其保存为“日期”变量：
Dim dDate as Date
dDate = dr("CurrentVersionDate")
Then I send these variables to another stored procedure to enter into the 'History' table:
然后我将这些变量发送到另一个存储过程以进入“历史”表：
Execute sp_InsertHistory '" & ID & "','" & strName& "','" & strFile & "','" & intVersion & "','" & dDate & "'"
I declare the date variable in the stored procedure as:
我将存储过程中的日期变量声明为：
@Date datetime,
with the insert statement:
使用插入语句：
INSERT INTO History
(ID, Name, FileName, FileVersion, VersionDate)
VALUES
(@ID, @Name, @FileName, @FileVersion, @Date)
The 'History' table has the same setup as the 'Current' table. Stepping through the code, this error is caught:
“历史”表与“当前”表具有相同的设置。单步执行代码，发现这个错误：
Error converting data type varchar to datetime
Surely the variables isn't varchar? And even if it is, the string is surely in the correct format for SQLserver because I'm just selecting the date value SQL has given! Any thoughts?
当然变量不是varchar？即使是这样，该字符串也肯定是 SQLserver 的正确格式，因为我只是选择 SQL 给出的日期值！有什么想法吗？
采纳答案by SysDragon
Since you are putting the date between quotes, is a varchar '" & dDate & "'", but I think the problem you have with the conversion could be that you are retrieving the date in your local format and you have to insert the date in the SQL Server in his own format.
由于您将日期放在引号之间，因此是 varchar '" & dDate & "'"，但我认为您在转换时遇到的问题可能是您正在以本地格式检索日期，并且您必须以他自己的格式在 SQL Server 中插入日期.
Try inserting the date as a string in this format: dDate.ToString("MM/dd/yyyy HH:mm:ss")
尝试以这种格式将日期作为字符串插入： dDate.ToString("MM/dd/yyyy HH:mm:ss")
回答by Alexey A.
the string is surely in the correct format for SQLserver
该字符串肯定是 SQLserver 的正确格式
How do you know that? To be safe, I'd use string in unambiguous format: YYYY-MM-DDTHH:MM:SS, e.g. 2013-05-13T01:01:00.
你怎么知道？为安全起见，我会使用明确格式的字符串：YYYY-MM-DDTHH:MM:SS，例如2013-05-13T01:01:00.
