将转换后的 varchar 插入 datetime sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13600924/
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
Insert converted varchar into datetime sql
提问by el shorty
I need to insert a varcharin my table. The type in the table is a datetimeso I need to convert it. I didn't think this would be to big of a problem however it keeps inserting 1900-01-01 00:00:00.000instead of the date I want. When I do a select with my converted date it does show me the correct date.
我需要varchar在我的表中插入一个。表中的类型是 adatetime所以我需要转换它。我不认为这会是一个大问题，但是它不断插入1900-01-01 00:00:00.000而不是我想要的日期。当我使用转换后的日期进行选择时，它确实显示了正确的日期。
I'll show you the code:
我将向您展示代码：
INSERT INTO Item (CategoryId, [Date], Content, CreatedOn)
SELECT
CategoryId, Convert(datetime, '28/11/2012', 103), Content, GetDate()
FROM
Item i
JOIN
Category c ON i.CategoryId = c.Id
JOIN
Division d ON d.Id = c.DivisionId
WHERE
Date = Convert(datetime, '31/03/2005', 103)
AND d.Id = '142aaddf-5b63-4d53-a331-8eba9b0556c4'
The where clause works perfectly and gives me the filtered items I need, all data is correctly inserted except for the converted date. The gives like I said 1900-...
where 子句工作得很好，并为我提供了我需要的过滤项目，除转换日期外，所有数据都已正确插入。就像我说的 1900-...
If I just do the select so:
如果我只是这样选择：
SELECT CategoryId, Convert(datetime, '28/11/2012', 103), Content, GetDate()
FROM Item i
JOIN Category c ON i.CategoryId = c.Id
JOIN Division d ON d.Id = c.DivisionId
WHERE Date = Convert(datetime, '31/03/2005', 103) AND d.Id = '142aaddf-5b63-4d53-a331-8eba9b0556c4'
I get the correct date being: 2012-11-28 00:00:00.000. I have tried to use a different conversion like:
我得到正确的日期是：2012-11-28 00:00:00.000。我尝试使用不同的转换，例如：
Convert(datetime, '20121128')
But that just gives the same problem. Anyone that sees what I'm doing wrong?
但这只会带来同样的问题。任何人都看到我做错了什么？
Thx
谢谢
回答by marc_s
If you must use a string-based date format, you should pick one that is safeand works in every SQL Server instance, regardless of date format, language and regional settings.
如果必须使用基于字符串的日期格式，则应选择一种安全且适用于每个 SQL Server 实例的日期格式，而不管日期格式、语言和区域设置如何。
That format is known as ISO-8601format and it's either
这种格式被称为ISO-8601格式，它要么是
YYYYMMDD (note: **NO** dashes!)
or
或者
YYYY-MM-DDTHH:MM:SSS
for a DATETIMEcolumn.
为一DATETIME列。
So instead of
所以代替
Convert(datetime, '28/11/2012', 103)
you should use
你应该使用
CAST('20121128' AS DATETIME)
and then you should be fine.
然后你应该没事。
If you're on SQL Server 2008- you could also look into using DATE(instead of DATETIME) for cases when you only need the date (no time portion). That would be even easier than using DATETIMEand having the time portion always be 00:00:00
如果您使用的是 SQL Server 2008-当您只需要日期（没有时间部分）时，您还可以考虑使用DATE（而不是DATETIME）。这比使用DATETIME和始终保持时间部分更容易00:00:00
回答by michael.therrien
Just wanted to throw out the fact that I found this SO Question (and many others like it) years later while trying to find the answer to my problem.
只是想抛出一个事实，即我在多年后试图找到我的问题的答案时发现了这个 SO Question（以及许多其他类似的问题）。
If you are troubleshooting this inside a stored procedure, the parameter of the stored procedure is where you need to modify. The programmer who created the code I'm troubleshooting had @ddt CHAR(10)and no modifications inside the stored procedure resolved the problem. I had to change the parameter to @ddt smalldatetime.
如果您在存储过程中对此进行故障排除，则需要修改存储过程的参数。创建我正在排除故障的代码的程序员拥有@ddt CHAR(10)并且在存储过程中没有修改解决了问题。我不得不将参数更改为@ddt smalldatetime.
