如何仅从 SQL Server DateTime 数据类型返回日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/113045/
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 return only the Date from a SQL Server DateTime datatype
提问by eddiegroves
SELECT GETDATE()
Returns: 2008-09-22 15:24:13.790
返回： 2008-09-22 15:24:13.790
I want that date part without the time part: 2008-09-22 00:00:00.000
我想要没有时间部分的日期部分： 2008-09-22 00:00:00.000
How can I get that?
我怎样才能得到它？
回答by aku
On SQL Server 2008and higher, you should CONVERTto date:
以后SQL Server 2008，你应该CONVERT：
SELECT CONVERT(date, getdate())
On older versions, you can do the following:
在旧版本上，您可以执行以下操作：
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
for example
例如
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
gives me
给我
2008-09-22 00:00:00.000
Pros:
优点：
- No
varchar<->datetimeconversions required - No need to think about
locale
- 无需
varchar<->datetime转换 - 无需考虑
locale
As suggested by Michael
正如迈克尔所建议的
Use this variant: SELECT DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
使用这个变体： SELECT DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
select getdate()
SELECT DATEADD(hh, DATEDIFF(hh, 0, getdate()), 0)
SELECT DATEADD(hh, 0, DATEDIFF(hh, 0, getdate()))
SELECT DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, getdate()))
SELECT DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)
SELECT DATEADD(mm, 0, DATEDIFF(mm, 0, getdate()))
SELECT DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)
SELECT DATEADD(yy, 0, DATEDIFF(yy, 0, getdate()))
Output:
输出：
2019-04-19 08:09:35.557
2019-04-19 08:00:00.000
4763-02-17 00:00:00.000
2019-04-19 00:00:00.000
2019-04-19 00:00:00.000
2019-04-01 00:00:00.000
1903-12-03 00:00:00.000
2019-01-01 00:00:00.000
1900-04-30 00:00:00.000
回答by BenR
SQLServer 2008 now has a 'date' data type which contains only a date with no time component. Anyone using SQLServer 2008 and beyond can do the following:
SQLServer 2008 现在有一个“日期”数据类型，它只包含一个没有时间组件的日期。使用 SQLServer 2008 及更高版本的任何人都可以执行以下操作：
SELECT CONVERT(date, GETDATE())
回答by abatishchev
If using SQL 2008 and above:
如果使用 SQL 2008 及更高版本：
select cast(getdate() as date)
回答by Ricardo C
DATEADD and DATEDIFF are better than CONVERTing to varchar. Both queries have the same execution plan, but execution plans are primarly about dataaccess strategies and do not always reveal implicit costs involved in the CPU time taken to perform all the pieces. If both queries are run against a table with millions of rows, the CPU time using DateDiff can be close to 1/3rd of the Convert CPU time!
DATEADD 和 DATEDIFF 比转换为 varchar 更好。两个查询具有相同的执行计划，但执行计划主要是关于数据访问策略，并不总是揭示执行所有部分所花费的 CPU 时间所涉及的隐性成本。如果两个查询都针对具有数百万行的表运行，则使用 DateDiff 的 CPU 时间可能接近 Convert CPU 时间的 1/3！
To see execution plans for queries:
查看查询的执行计划：
set showplan_text on
GO
Both DATEADD and DATEDIFF will execute a CONVERT_IMPLICIT.
DATEADD 和 DATEDIFF 都将执行 CONVERT_IMPLICIT。
Although the CONVERT solution is simpler and easier to read for some, it isslower. There is no need to cast back to datetime (this is implicitly done by the server). There is also no real need in the DateDiff method for DateAdd afterward as the integer result will also be implicitly converted back to datetime.
尽管 CONVERT 解决方案对某些人来说更简单、更容易阅读，但它更慢。不需要转换回日期时间（这是由服务器隐式完成的）。之后也没有真正需要 DateAdd 的 DateDiff 方法，因为整数结果也将隐式转换回日期时间。
SELECT CONVERT(varchar, MyDate, 101) FROM DatesTable
SELECT CONVERT(varchar, MyDate, 101) FROM DatesTable
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT(varchar(30),[TEST].[dbo].[DatesTable].[MyDate],101)))
|--Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, MyDate)) FROM DatesTable
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, MyDate)) FROM DatesTable
|--Compute Scalar(DEFINE:([Expr1004]=dateadd(day,(0),CONVERT_IMPLICIT(datetime,datediff(day,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[TEST].[dbo].[DatesTable].[MyDate],0)),0))))
|--Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))
Using FLOOR() as @digi suggested has performance closer to DateDiff, but is not recommended as casting the datetime data type to float and back does not always yield the original value.
使用 @digi 建议的 FLOOR() 具有更接近 DateDiff 的性能，但不建议使用，因为将日期时间数据类型转换为浮动并返回并不总是产生原始值。
Remember guys: Don't believe anyone. Look at the performance statistics, and test it yourself!
记住伙计们：不要相信任何人。查看性能统计数据，自己测试一下！
Be careful when you're testing your results. Selecting many rows to the client will hide the performance difference becauses it takes longer to send the rows over the network than it does to perform the calculations. So make sure that the work for all the rows is done by the server but there is no rowset sent to the client.
测试结果时要小心。向客户端选择多行将隐藏性能差异，因为通过网络发送行比执行计算需要更长的时间。因此，请确保所有行的工作都由服务器完成，但没有发送到客户端的行集。
There seems to be confusion for some people about when cache optimization affects queries. Running two queries in the same batch or in separate batches has no effect on caching. So you can either expire the cache manually or simply run the queries back and forth multiple times. Any optimization for query #2 would also affect any subsequent queries, so throw out execution #1 if you like.
有些人似乎对缓存优化何时影响查询感到困惑。在同一批次或不同批次中运行两个查询对缓存没有影响。因此，您可以手动使缓存过期，也可以简单地多次来回运行查询。查询 #2 的任何优化也会影响任何后续查询，因此如果您愿意，请放弃执行 #1。
Here is full test script and performance resultsthat prove DateDiff is substantially faster than converting to varchar.
这是完整的测试脚本和性能结果，证明 DateDiff 比转换为 varchar 快得多。
回答by Nescio
回答by Cade Roux
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
回答by DaveK
You can use the CONVERTfunction to return only the date. See the link(s) below:
您可以使用该CONVERT函数仅返回日期。请参阅以下链接：
Date and Time Manipulation in SQL Server 2000
The syntax for using the convert function is:
使用 convert 函数的语法是：
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
回答by Mahesh ML
For return in date format
以日期格式返回
CAST(OrderDate AS date)
CAST(OrderDate AS 日期)
The above code will work in sql server 2010
上面的代码将在 sql server 2010 中工作
It will return like 12/12/2013
它会像 12/12/2013 一样返回
For SQL Server 2012 use the below code
对于 SQL Server 2012，请使用以下代码
CONVERT(VARCHAR(10), OrderDate , 111)
回答by Stephon Johns
If you need the result as a varchar, you should go through
如果你需要结果作为varchar，你应该通过
SELECT CONVERT(DATE, GETDATE()) --2014-03-26
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) --2014/03/26
which is already mentioned above.
上面已经提到了。
If you need result in date and time format, you should use any of the queries below
如果您需要日期和时间格式的结果，您应该使用以下任何查询
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 111)) AS OnlyDate2014-03-26 00:00:00.000
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 112)) AS OnlyDate2014-03-26 00:00:00.000
DECLARE @OnlyDate DATETIME SET @OnlyDate = DATEDIFF(DD, 0, GETDATE()) SELECT @OnlyDate AS OnlyDate2014-03-26 00:00:00.000
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 111)) AS OnlyDate2014-03-26 00:00:00.000
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 112)) AS OnlyDate2014-03-26 00:00:00.000
DECLARE @OnlyDate DATETIME SET @OnlyDate = DATEDIFF(DD, 0, GETDATE()) SELECT @OnlyDate AS OnlyDate2014-03-26 00:00:00.000
回答by Rushda
SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),103) --21/09/2011
SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),101) --09/21/2011
SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),111) --2011/09/21
SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),107) --Sep 21, 2011
