In MS SQL 2000 and 2005, given a datetime such as '2008-09-25 12:34:56' what is the most efficient way to get a datetime containing only '2008-09-25'?
在MS SQL 2000和2005中，给定一个日期时间，比如“2008-09-25 12:34:56”，获得一个只包含“2008-09-25”的日期时间的最有效方法是什么?
Duplicated here.
复制在这里。
11 个解决方案
#1
109
I must admit I hadn't seen the floor-float conversion shown by Matt before. I had to test this out.
我得承认我以前没见过马特展示的浮动转换。我必须测试一下。
I tested a pure select (which will return Date and Time, and is not what we want), the reigning solution here (floor-float), a common 'naive' one mentioned here (stringconvert) and the one mentioned here that I was using (as I thought it was the fastest).
我测试了纯select(它将返回日期和时间，而不是我们想要的)、这里的主流解决方案(floor-float)、这里提到的常见的“幼稚”解决方案(stringconvert)和这里提到的那个我正在使用的(因为我认为它是最快的)。
I tested the queries on a test-server MS SQL Server 2005 running on a Win 2003 SP2 Server with a Xeon 3GHz CPU running on max memory (32 bit, so that's about 3.5 Gb). It's night where I am so the machine is idling along at almost no load. I've got it all to myself.
我测试了在win2003 SP2服务器上运行的测试服务器MS SQL Server 2005上的查询，在最大内存上运行一个Xeon 3GHz CPU(32位，所以大约是3.5 Gb)。我现在是晚上，所以这台机器几乎空转着。我全靠自己了。
Here's the log from my test-run selecting from a large table containing timestamps varying down to the millisecond level. This particular dataset includes dates ranging over 2.5 years. The table itself has over 130 million rows, so that's why I restrict to the top million.
下面是我在一个包含时间戳的大表中进行的测试运行选择的日志。这个特殊的数据集包含了超过2.5年的日期。表本身有超过1.3亿行，所以我将限制在前百万行。
SELECT TOP 1000000 CRETS FROM tblMeasureLogv2
SELECT TOP 1000000 CAST(FLOOR(CAST(CRETS AS FLOAT)) AS DATETIME) FROM tblMeasureLogv2
SELECT TOP 1000000 CONVERT(DATETIME, CONVERT(VARCHAR(10), CRETS, 120) , 120) FROM tblMeasureLogv2
SELECT TOP 1000000 DATEADD(DAY, DATEDIFF(DAY, 0, CRETS), 0) FROM tblMeasureLogv2
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server解析和编译时间:CPU时间= 0 ms，运行时间= 1 ms。
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1000000行)扫描计数1，逻辑读取4752，物理读取0，读前读取0,lob逻辑读取0,lob物理读取0,lob读取前读取0。
SQL Server Execution Times: CPU time = 422 ms, elapsed time = 33803 ms.
SQL Server执行时间:CPU时间= 422 ms，运行时间= 33803 ms。
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1000000行)扫描计数1，逻辑读取4752，物理读取0，读前读取0,lob逻辑读取0,lob物理读取0,lob读取前读取0。
SQL Server Execution Times: CPU time = 625 ms, elapsed time = 33545 ms.
SQL Server执行时间:CPU时间= 625 ms，运行时间= 33545 ms。
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1000000行)扫描计数1，逻辑读取4752，物理读取0，读前读取0,lob逻辑读取0,lob物理读取0,lob读取前读取0。
SQL Server Execution Times: CPU time = 1953 ms, elapsed time = 33843 ms.
SQL Server执行时间:CPU时间= 1953 ms，运行时间= 33843 ms。
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1000000行)扫描计数1，逻辑读取4752，物理读取0，读前读取0,lob逻辑读取0,lob物理读取0,lob读取前读取0。
SQL Server Execution Times: CPU time = 531 ms, elapsed time = 33440 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server执行时间:CPU时间= 531 ms，运行时间= 33440 ms。SQL Server解析和编译时间:CPU时间= 0 ms，运行时间= 1 ms。
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server执行时间:CPU时间= 0 ms，运行时间= 1 ms。
What are we seeing here?
我们看到了什么?
Let's focus on the CPU time (we're looking at conversion), and we can see that we have the following numbers:
让我们关注CPU时间(我们正在研究转换)，我们可以看到我们有以下数字:
Pure-Select: 422
Floor-cast: 625
String-conv: 1953
DateAdd: 531
From this it looks to me like the DateAdd (at least in this particular case) is slightly faster than the floor-cast method.
在我看来，DateAdd(至少在这个特殊的例子中)比floor-cast方法稍微快一些。
Before you go there, I ran this test several times, with the order of the queries changed, same-ish results.
在此之前，我多次运行这个测试，查询的顺序发生了改变，结果也是一样的。
Is this something strange on my server, or what?
我的服务器上有什么奇怪的东西吗?
#2
22
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
DateDiff(Day, 0, GetDate()) is the same as DateDiff(Day, '1900-01-01', GetDate())
DateDiff(Day, 0, GetDate()))与DateDiff(Day， '1900-01-01'， GetDate()相同
Since DateDiff returns an integer, you will get the number of days that have elapsed since Jan 1, 1900. You then add that integer number of days to Jan 1, 1900. The net effect is removing the time component.
由于DateDiff返回一个整数，您将获得自1900年1月1日以来经过的天数。然后将该整数天数添加到1900年1月1日。最终的效果是去掉了时间分量。
I should also mention that this method works for any date/time part (like year, quarter, month, day, hour, minute, and second).
我还要指出，这种方法适用于任何日期/时间部分(比如年、季度、月、日、小时、分钟和秒)。
Select DateAdd(Year, DateDiff(Year, 0, GetDate()), 0)
Select DateAdd(Quarter, DateDiff(Quarter, 0, GetDate()), 0)
Select DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
Select DateAdd(Hour, DateDiff(Hour, 0, GetDate()), 0)
Select DateAdd(Second, DateDiff(Second, '20000101', GetDate()), '20000101')
The last one, for seconds, requires special handling. If you use Jan 1, 1900 you will get an error.
最后一个，以秒为单位，需要特殊处理。如果你使用1900年1月1日，你会得到一个错误。
Difference of two datetime columns caused overflow at runtime.
两个datetime列的差异导致了运行时的溢出。
You can circumvent this error by using a different reference date (like Jan 1, 2000).
您可以通过使用不同的引用日期(如2000年1月1日)来避免这个错误。
#3
12
select cast(floor(cast(@datetime as float)) as datetime)
Works because casting a datetime to float gives the number of days (including fractions of a day) since Jan 1, 1900. Flooring it removes the fractional days and leaves the number of whole days, which can then be cast back to a datetime.
之所以这么做，是因为向float强制转换datetime提供自1900年1月1日以来的天数(包括一天的小数部分)。地板去除部分天数，留下整个天数，然后可以将其转换回日期时间。
#4
6
in SQL server 2012 use
在SQL server 2012中使用
select cast(getdate() as date)
#5
1
select cast(getdate()as varchar(11))as datetime
#6
0
To get YYYY-MM-DD, use:
YYYY-MM-DD,使用:
select convert(varchar(10), getdate(), 120)
Edit: Oops, he wants a DateTime instead of a string. The equivalent of TRUNC() in Oracle. You can take what I posted and cast back to a DateTime:
编辑:糟糕，他想要一个DateTime而不是string。在Oracle中相当于TRUNC()。你可以把我贴出的内容转换回一个DateTime:
select convert(datetime, convert(varchar(10), getdate(), 120) , 120)
#7
0
CONVERT, FLOOR ,and DATEDIFF will perform just the same.
转换、层和DATEDIFF将执行相同的操作。
How to return the date part only from a SQL Server datetime datatype
如何仅从SQL Server datetime数据类型返回日期部分
#8
0
Three methods described in the link below. I haven't performance tested them to determine which is quickest.
下面的链接中描述了三种方法。我还没有通过性能测试来确定哪一个最快。
http://www.blackwasp.co.uk/SQLDateFromDateTime.aspx
http://www.blackwasp.co.uk/SQLDateFromDateTime.aspx
#9
0
CAST(FLOOR(CAST(yourdate AS DECIMAL(12, 5))) AS DATETIME) performs the best by far. you can see the proof & tests when getting the date without time in sql server
CAST(FLOOR(CAST) (yourdate AS DECIMAL(12,5)) AS DATETIME))。在sql server中获取日期时，您可以看到证明和测试
#10
-1
What About SELECT CAST(CASt(GETDATE() AS int) AS DATETIME)??
选择CAST(CAST(GETDATE()作为int)作为DATETIME)怎么样?
#11
-1
CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
#1
109
I must admit I hadn't seen the floor-float conversion shown by Matt before. I had to test this out.
我得承认我以前没见过马特展示的浮动转换。我必须测试一下。
I tested a pure select (which will return Date and Time, and is not what we want), the reigning solution here (floor-float), a common 'naive' one mentioned here (stringconvert) and the one mentioned here that I was using (as I thought it was the fastest).
我测试了纯select(它将返回日期和时间，而不是我们想要的)、这里的主流解决方案(floor-float)、这里提到的常见的“幼稚”解决方案(stringconvert)和这里提到的那个我正在使用的(因为我认为它是最快的)。
I tested the queries on a test-server MS SQL Server 2005 running on a Win 2003 SP2 Server with a Xeon 3GHz CPU running on max memory (32 bit, so that's about 3.5 Gb). It's night where I am so the machine is idling along at almost no load. I've got it all to myself.
我测试了在win2003 SP2服务器上运行的测试服务器MS SQL Server 2005上的查询，在最大内存上运行一个Xeon 3GHz CPU(32位，所以大约是3.5 Gb)。我现在是晚上，所以这台机器几乎空转着。我全靠自己了。
Here's the log from my test-run selecting from a large table containing timestamps varying down to the millisecond level. This particular dataset includes dates ranging over 2.5 years. The table itself has over 130 million rows, so that's why I restrict to the top million.
下面是我在一个包含时间戳的大表中进行的测试运行选择的日志。这个特殊的数据集包含了超过2.5年的日期。表本身有超过1.3亿行，所以我将限制在前百万行。
SELECT TOP 1000000 CRETS FROM tblMeasureLogv2
SELECT TOP 1000000 CAST(FLOOR(CAST(CRETS AS FLOAT)) AS DATETIME) FROM tblMeasureLogv2
SELECT TOP 1000000 CONVERT(DATETIME, CONVERT(VARCHAR(10), CRETS, 120) , 120) FROM tblMeasureLogv2
SELECT TOP 1000000 DATEADD(DAY, DATEDIFF(DAY, 0, CRETS), 0) FROM tblMeasureLogv2
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server解析和编译时间:CPU时间= 0 ms，运行时间= 1 ms。
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1000000行)扫描计数1，逻辑读取4752，物理读取0，读前读取0,lob逻辑读取0,lob物理读取0,lob读取前读取0。
SQL Server Execution Times: CPU time = 422 ms, elapsed time = 33803 ms.
SQL Server执行时间:CPU时间= 422 ms，运行时间= 33803 ms。
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1000000行)扫描计数1，逻辑读取4752，物理读取0，读前读取0,lob逻辑读取0,lob物理读取0,lob读取前读取0。
SQL Server Execution Times: CPU time = 625 ms, elapsed time = 33545 ms.
SQL Server执行时间:CPU时间= 625 ms，运行时间= 33545 ms。
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1000000行)扫描计数1，逻辑读取4752，物理读取0，读前读取0,lob逻辑读取0,lob物理读取0,lob读取前读取0。
SQL Server Execution Times: CPU time = 1953 ms, elapsed time = 33843 ms.
SQL Server执行时间:CPU时间= 1953 ms，运行时间= 33843 ms。
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1000000行)扫描计数1，逻辑读取4752，物理读取0，读前读取0,lob逻辑读取0,lob物理读取0,lob读取前读取0。
SQL Server Execution Times: CPU time = 531 ms, elapsed time = 33440 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server执行时间:CPU时间= 531 ms，运行时间= 33440 ms。SQL Server解析和编译时间:CPU时间= 0 ms，运行时间= 1 ms。
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server执行时间:CPU时间= 0 ms，运行时间= 1 ms。
What are we seeing here?
我们看到了什么?
Let's focus on the CPU time (we're looking at conversion), and we can see that we have the following numbers:
让我们关注CPU时间(我们正在研究转换)，我们可以看到我们有以下数字:
Pure-Select: 422
Floor-cast: 625
String-conv: 1953
DateAdd: 531
From this it looks to me like the DateAdd (at least in this particular case) is slightly faster than the floor-cast method.
在我看来，DateAdd(至少在这个特殊的例子中)比floor-cast方法稍微快一些。
Before you go there, I ran this test several times, with the order of the queries changed, same-ish results.
在此之前，我多次运行这个测试，查询的顺序发生了改变，结果也是一样的。
Is this something strange on my server, or what?
我的服务器上有什么奇怪的东西吗?
#2
22
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
DateDiff(Day, 0, GetDate()) is the same as DateDiff(Day, '1900-01-01', GetDate())
DateDiff(Day, 0, GetDate()))与DateDiff(Day， '1900-01-01'， GetDate()相同
Since DateDiff returns an integer, you will get the number of days that have elapsed since Jan 1, 1900. You then add that integer number of days to Jan 1, 1900. The net effect is removing the time component.
由于DateDiff返回一个整数，您将获得自1900年1月1日以来经过的天数。然后将该整数天数添加到1900年1月1日。最终的效果是去掉了时间分量。
I should also mention that this method works for any date/time part (like year, quarter, month, day, hour, minute, and second).
我还要指出，这种方法适用于任何日期/时间部分(比如年、季度、月、日、小时、分钟和秒)。
Select DateAdd(Year, DateDiff(Year, 0, GetDate()), 0)
Select DateAdd(Quarter, DateDiff(Quarter, 0, GetDate()), 0)
Select DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
Select DateAdd(Hour, DateDiff(Hour, 0, GetDate()), 0)
Select DateAdd(Second, DateDiff(Second, '20000101', GetDate()), '20000101')
The last one, for seconds, requires special handling. If you use Jan 1, 1900 you will get an error.
最后一个，以秒为单位，需要特殊处理。如果你使用1900年1月1日，你会得到一个错误。
Difference of two datetime columns caused overflow at runtime.
两个datetime列的差异导致了运行时的溢出。
You can circumvent this error by using a different reference date (like Jan 1, 2000).
您可以通过使用不同的引用日期(如2000年1月1日)来避免这个错误。
#3
12
select cast(floor(cast(@datetime as float)) as datetime)
Works because casting a datetime to float gives the number of days (including fractions of a day) since Jan 1, 1900. Flooring it removes the fractional days and leaves the number of whole days, which can then be cast back to a datetime.
之所以这么做，是因为向float强制转换datetime提供自1900年1月1日以来的天数(包括一天的小数部分)。地板去除部分天数，留下整个天数，然后可以将其转换回日期时间。
#4
6
in SQL server 2012 use
在SQL server 2012中使用
select cast(getdate() as date)
#5
1
select cast(getdate()as varchar(11))as datetime
#6
0
To get YYYY-MM-DD, use:
YYYY-MM-DD,使用:
select convert(varchar(10), getdate(), 120)
Edit: Oops, he wants a DateTime instead of a string. The equivalent of TRUNC() in Oracle. You can take what I posted and cast back to a DateTime:
编辑:糟糕，他想要一个DateTime而不是string。在Oracle中相当于TRUNC()。你可以把我贴出的内容转换回一个DateTime:
select convert(datetime, convert(varchar(10), getdate(), 120) , 120)
#7
0
CONVERT, FLOOR ,and DATEDIFF will perform just the same.
转换、层和DATEDIFF将执行相同的操作。
How to return the date part only from a SQL Server datetime datatype
如何仅从SQL Server datetime数据类型返回日期部分
#8
0
Three methods described in the link below. I haven't performance tested them to determine which is quickest.
下面的链接中描述了三种方法。我还没有通过性能测试来确定哪一个最快。
http://www.blackwasp.co.uk/SQLDateFromDateTime.aspx
http://www.blackwasp.co.uk/SQLDateFromDateTime.aspx
#9
0
CAST(FLOOR(CAST(yourdate AS DECIMAL(12, 5))) AS DATETIME) performs the best by far. you can see the proof & tests when getting the date without time in sql server
CAST(FLOOR(CAST) (yourdate AS DECIMAL(12,5)) AS DATETIME))。在sql server中获取日期时，您可以看到证明和测试
#10
-1
What About SELECT CAST(CASt(GETDATE() AS int) AS DATETIME)??
选择CAST(CAST(GETDATE()作为int)作为DATETIME)怎么样?
#11
-1
CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]