SQL 如何仅将日期时间转换为日期（时间设置为 00:00:00.000）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1040654/
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
How to convert datetime to date only (with time set to 00:00:00.000)
提问by Saobi
I have a string '2009-06-24 09:52:43.000', which I need to insert to a DateTime column of a table.
我有一个字符串 '2009-06-24 09:52:43.000'，我需要将其插入到表的 DateTime 列中。
But I don't care about the time, just want to insert it as 2009-06-24 00:00:00.000
但我不在乎时间，只想插入为 2009-06-24 00:00:00.000
How can I do that in T-SQL?
我怎样才能在 T-SQL 中做到这一点？
回答by GSerg
For SQL Server 2005 and below:
对于 SQL Server 2005 及更低版本：
CONVERT(varchar(8), @ParamDate, 112) -- Supported way
CAST(FLOOR(CAST(@ParamDate AS float)) AS DATETIME) -- Unsupported way
For SQL Server 2008 and above:
对于 SQL Server 2008 及更高版本：
CAST(@ParamDate AS DATE)
回答by Kevin Newman
declare @originalDate datetime
select @originalDate = '2009-06-24 09:52:43.000'
declare @withoutTime datetime
select @withoutTime = dateadd(d, datediff(d, 0, @originalDate), 0)
select @withoutTime
回答by DBAndrew
SELECT CAST(CONVERT(VARCHAR,GETDATE(),102) AS DATETIME)
SELECT CAST(CONVERT(VARCHAR(10),'2009-06-24 09:52:43.000',102) AS DATETIME)
回答by sanBV
An enhancement to the unsupported version:
I am not sure if this may effect any performance. getdate()is an input timestamp in my query.
对不受支持版本的增强：我不确定这是否会影响任何性能。getdate()是我查询中的输入时间戳。
select cast(cast(getdate() as DATE) as DATETIME)
select cast(cast(getdate() as DATE) as DATETIME)
回答by Tyler Facker
I have found that casting as a date, then to a datetime to be very efficient and intuitive.
我发现将日期转换为日期，然后转换为日期时间非常有效和直观。
SELECT CAST(CAST(GETDATE() as date) as datetime)
回答by TimF
James is correct. If you're starting off with a string, and the format will always be what you say it is, then you keep it simple and efficient. Use LEFT( @StrDate, 10)and CONVERTthat to your datetime value. Done.
詹姆斯是对的。如果您从一个字符串开始，并且格式始终是您所说的那样，那么您可以保持它的简单和高效。使用LEFT( @StrDate, 10)和CONVERT，为您的时间值。完毕。
If your input string could be anyvalid date/time format, then you have to use CONVERT(datetime, @StrDate)first. After that you go with what Bing just said to strip off the time part.
如果您的输入字符串可以是任何有效的日期/时间格式，那么您必须先使用CONVERT(datetime, @StrDate)。之后你就按照 Bing 刚刚说的去掉时间部分。
回答by Scott Ivey
cast it to a date, and then you can use CONVERT to get just the date.
将其转换为日期，然后您可以使用 CONVERT 来获取日期。
INSERT MyTable(Column1)
SELECT CONVERT(CHAR(8), CAST('2009-06-24 09:52:43.000' AS DATETIME), 112)
回答by James Conigliaro
If you will always have the date in the same format, i.e. yyyy-MM-DD you can grab the first 10 characters if the value and insert that which is the equivelant of 00:00:00.0000 time for that date.
如果您将始终使用相同格式的日期，即 yyyy-MM-DD，您可以获取前 10 个字符，如果该值并插入该日期的 00:00:00.0000 时间等值。
select left('2009-12-32 4:32:00',10)
This is a very efficient way to do this as it does't require converting data types HOWEVER, it does require that the date will always be formatted with a four digit year and two digit day & month.
这是一种非常有效的方法，因为它不需要转换数据类型，但是，它确实需要将日期格式化为四位数的年份和两位数的日期和月份。
回答by Jeremy Smyth
A variety of hacks:
各种黑客：
- Convert your string to a datetime, then back again using the optional "style" parameter to
convertto convert your datetime to a string using just the date portion - use
substringto chop off the end - round the datetime using
floor
- 将您的字符串转换为日期时间，然后再次使用可选的“样式”参数
convert将您的日期时间转换为仅使用日期部分的字符串 - 使用
substring砍掉的端 - 使用四舍五入日期时间
floor
回答by Eric H.
Probably a cleaner and more portable way to do this, but my years old idiom is:
可能是一种更干净、更便携的方式来做到这一点，但我多年的习惯用法是：
insert into tbl (date_column)
select convert(varchar, convert (datetime, '2009-06-24 09:52:43.000'), 101)
