What's the best way to truncate a datetime value (as to remove hours minutes and seconds) in SQL Server 2008?
在SQL Server 2008中截断一个datetime值的最佳方法是什么(删除小时数和秒)?
For example:
例如:
declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)
-----------------------
2009-05-28 00:00:00.000
13 个解决方案
#1
412
This continues to frequently gather additional votes, even several years later, and so I need to update it for modern versions of Sql Server. For Sql Server 2008 and later, it's simple:
这将继续频繁地收集更多的选票，甚至几年之后，所以我需要更新Sql Server的现代版本。对于Sql Server 2008和稍后，它很简单:
cast(getDate() As Date)
Note that the last three paragraphs near the bottom still apply, and you often need to take a step back and find a way to avoid the cast in the first place.
请注意，底部的最后三段仍然适用，并且您经常需要后退一步，并找到一种方法来避免最初的转换。
But there are other ways to accomplish this, too. Here are the most common.
但也有其他的方法来实现这一点。这是最常见的。
The correct way (new since Sql Server 2008):
正确的方法(Sql Server 2008):
cast(getdate() As Date)
The correct way (old):
正确的方式(旧):
dateadd(dd, datediff(dd,0, getDate()), 0)
This is older now, but it's still worth knowing because it can also easily adapt for other time points, like the first moment of the month, minute, hour, or year.
现在已经变老了，但还是值得一看的，因为它也可以很容易地适应其他时间点，比如一个月、一分钟、一小时或一年的第一个时刻。
This correct way uses documented functions that are part of the ansi standard and are guaranteed to work, but it can be somewhat slower. It works by finding how many days there are from day 0 to the current day, and adding that many days back to day 0. It will work no matter how your datetime is stored and no matter what your locale is.
这种正确的方法使用了ansi标准的一部分的文档化函数，并保证可以工作，但是它可以稍微慢一些。它的工作原理是找出从第0天到现在的天数，并将其添加到第0天。无论您的datetime是如何存储的，无论您的语言环境是什么，它都可以工作。
The fast way:
快的方法:
cast(floor(cast(getdate() as float)) as datetime)
This works because datetime columns are stored as 8-byte binary values. Cast them to float, floor them to remove the fraction, and the time portion of the values are gone when you cast them back to datetime. It's all just bit shifting with no complicated logic and it's very fast.
这是因为datetime列被存储为8字节的二进制值。将它们转换为浮点数，让它们删除分数，当您将它们返回到datetime时，值的时间部分就消失了。这都是一点点变化，没有复杂的逻辑，而且速度非常快。
Be aware this relies on an implementation detail Microsoft is free to change at any time, even in an automatic service update. It's also not very portable. In practice, it's very unlikely this implementation will change any time soon, but it's still important to be aware of the danger if you choose to use it. And now that we have the option to cast as a date, it's rarely necessary.
请注意，这依赖于实现细节，微软可以随时更改，即使是在自动服务更新中。它也不是很便携。实际上，这种实现在短时间内不太可能发生变化，但是如果您选择使用它，了解它的危险仍然很重要。现在我们有了选择的日期，这是很少有必要的。
The wrong way:
错误的方式:
cast(convert(char(11), getdate(), 113) as datetime)
The wrong way works by converting to a string, truncating the string, and converting back to a datetime. It's wrong, for two reasons: 1)it might not work across all locales and 2) it's about the slowest possible way to do this... and not just a little; it's like an order of magnitude or two slower than the other options.
错误的方法是转换成字符串，截断字符串，然后转换回datetime。这是错误的，原因有二:1)它可能不适用于所有的地方，2)它是最慢的可能的方法…不仅仅是一点点;它就像一个数量级或者两个比其他选项慢。
Update This has been getting some votes lately, and so I want to add to it that since I posted this I've seen some pretty solid evidence that Sql Server will optimize away the performance difference between "correct" way and the "fast" way, meaning you should now favor the former.
最近更新了一些选票，所以我想补充一点，自从我发布这篇文章以来，我已经看到了一些非常可靠的证据，表明Sql Server将优化“正确”的方式和“快速”的方式之间的性能差异，这意味着您现在应该支持前者。
In either case, you want to write your queries to avoid the need to do this in the first place. It's very rare that you should do this work on the database.
在这两种情况下，您都希望编写您的查询，以避免在一开始就需要这样做。在数据库上做这项工作是非常罕见的。
In most places, the database is already your bottleneck. It's generally the server that's the most expensive to add hardware to for performance improvements and the hardest one to get those additions right (you have to balance disks with memory, for example). It's also the hardest to scale outward, both technically and from a business standpoint; it's much easier technically to add a web or application server than a database server and even if that were false you don't pay $20,000+ per server license for IIS or apache.
在大多数地方，数据库已经成为您的瓶颈。通常情况下，为性能改进添加硬件的成本最高，而最困难的是要获得这些附加功能(例如，您必须平衡磁盘与内存)。从技术上讲，从商业角度来说，这也是最难做到的。比起数据库服务器，在技术上添加web或应用服务器要简单得多，即使是错误的，也不需要为IIS或apache支付$20,000+的服务器许可证。
The point I'm trying to make is that whenever possible you should do this work at the application level. The only time you should ever find yourself truncating a datetime on Sql Server is when you need to group by the day, and even then you should probably have an extra column set up as a computed column, maintained at insert/update time, or maintained in application logic. Get this index-breaking, cpu-heavy work off your database.
我想说的是，只要有可能，就应该在应用程序级别进行这项工作。唯一一次在Sql Server上截断一个datetime的时间是当您需要按天分组时，即使这样，您可能还应该设置一个额外的列，作为计算列，在插入/更新时间维护，或在应用程序逻辑中维护。从你的数据库中获取这个不可分割的、cpu繁重的工作。
#2
43
For SQL Server 2008 only
仅适用于SQL Server 2008。
CAST(@SomeDateTime AS Date)
Then cast it back to datetime if you want
然后将它转换回datetime，如果您需要的话。
CAST(CAST(@SomeDateTime AS Date) As datetime)
#3
18
Just for the sake of a more complete answer, here's a working way for truncating to any of the date parts down and including minutes (replace GETDATE() with the date to truncate).
为了得到更完整的答案，这里有一个用于截断任何日期部分的工作方法，包括分钟(将GETDATE()替换为截断日期)。
This is different from the accepted answer in that you can use not only dd (days), but any of the date parts (see here):
这与接受的答案不同，你不仅可以使用dd (days)，还可以使用任何日期部分(见此处):
dateadd(minute, datediff(minute, 0, GETDATE()), 0)
Note that in the expression above, the 0 is a constant date on the beginning of a year (1900-01-01). If you need to truncate to smaller parts, such as to seconds or milliseconds, you need to take a constant date which is closer to the date to be truncated to avoid an overflow.
注意，在上面的表达式中，0是一年开始的常数日期(1900-01-01)。如果您需要截断到较小的部分，比如秒或毫秒，您需要使用一个更接近日期的固定日期，以避免溢出。
#4
7
The snippet I found on the web when I had to do this was:
我在网上找到的片段是:
dateadd(dd,0, datediff(dd,0, YOURDATE))
e.g.
dateadd(dd,0, datediff(dd,0, getDate()))
#5
1
In SQl 2005 your trunc_date function could be written like this.
在SQl 2005中，您的trunc_date函数可以写成这样。
(1)
(1)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
CAST(FLOOR( CAST( @date AS FLOAT ) )AS DATETIME)
END
The first method is much much cleaner. It uses only 3 method calls including the final CAST() and performs no string concatenation, which is an automatic plus. Furthermore, there are no huge type casts here. If you can imagine that Date/Time stamps can be represented, then converting from dates to numbers and back to dates is a fairly easy process.
第一种方法要干净得多。它只使用3个方法调用，包括final CAST()，并且不执行字符串连接，这是一个自动加值。此外，这里没有大型类型的类型转换。如果您可以想象日期/时间戳可以表示，那么从日期转换为数字和返回日期是一个相当简单的过程。
(2)
(2)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
SELECT CONVERT(varchar, @date,112)
END
If you are concerned about microsoft's implementation of datetimes (2) or (3) might be ok.
如果您对微软的datetimes(2)或(3)的实现感到担心，那就可以了。
(3)
(3)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
SELECT CAST((STR( YEAR( @date ) ) + '/' +STR( MONTH( @date ) ) + '/' +STR( DAY(@date ) )
) AS DATETIME
END
Third, the more verbose method. This requires breaking the date into its year, month, and day parts, putting them together in "yyyy/mm/dd" format, then casting that back to a date. This method involves 7 method calls including the final CAST(), not to mention string concatenation.
第三，更详细的方法。这就要求将日期划分为年、月和日部分，将它们放在“yyyy/mm/dd”格式中，然后将其转换为日期。这个方法包括7个方法调用，包括final CAST()，更不用说字符串连接了。
#6
1
CONVERT(DATE, <yourdatetime>) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)
#7
1
you could just do this (SQL 2008):
你可以这样做(SQL 2008):
declare @SomeDate date = getdate()
声明@SomeDate date = getdate()
select @SomeDate
2009-05-28
2009-05-28
#8
0
select cast(floor(cast(getdate() as float)) as datetime) Reference this: http://microsoftmiles.blogspot.com/2006/11/remove-time-from-datetime-in-sql-server.html
选择cast(层(cast)，如datetime)，引用如下:http://microsoftmiles.blogspot.com/2006/11/remove- datetime-in- datetime-in- datetime-in- time-in-sql-server.html。
#9
0
For those of you who came here looking for a way to truncate a DATETIME field to something less than a whole day, for example every minute, you can use this:
对于你们中来到这里的人，想要找到一种方法，将DATETIME字段截断为少于一整天的东西，例如，每分钟，你可以用这个:
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) + (FLOOR((CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 1440.0) + (3.0/86400000.0)) / 1440.0 AS DATETIME)
so if today was 2010-11-26 14:54:43.123 then this would return 2010-11-26 14:54:00.000.
因此，如果今天是2010-11-26,14:54:43.123，那么这将返回2010-11-26 14:54。
To change the interval it trucates to, replace 1440.0 with the number of intervals in a day, for example:
为了改变它的时间间隔，用一天的时间间隔替换1440.0，例如:
24hrs = 24.0 (for every hour)
24hrs / 0.5hrs = 48.0 (for every half hour)
24hrs / (1/60) = 1440.0 (for every minute)
(Always put a .0 on the end to implicitly cast to a float.)
(总是将.0放在末尾，隐式转换为浮点数。)
For those of you wondering what the (3.0/86400000) is for in my calculation, SQL Server 2005 doesn't seem to cast from FLOAT to DATETIME accurately, so this adds 3 milliseconds before flooring it.
对于那些想知道(3.0/8640000)在我的计算中是什么的人，SQL Server 2005似乎并没有精确地从浮点数转换到DATETIME，所以在使用它之前，它会增加3毫秒。
#10
0
This query should give you result equivalent to trunc(sysdate) in Oracle.
这个查询应该在Oracle中给出相当于trunc(sysdate)的结果。
SELECT *
FROM your_table
WHERE CONVERT(varchar(12), your_column_name, 101)
= CONVERT(varchar(12), GETDATE(), 101)
Hope this helps!
希望这可以帮助!
#11
0
You can also extract date using Substring from the datetime variable and casting back to datetime will ignore time part.
您还可以从datetime变量中使用子字符串提取日期，并将其转换回datetime将忽略时间部分。
declare @SomeDate datetime = '2009-05-28 16:30:22'
SELECT cast(substring(convert(varchar(12),@SomeDate,111),0,12) as Datetime)
Also, you can access parts of datetime variable and merge them to a construct truncated date, something like this:
此外，您还可以访问datetime变量的部分，并将它们合并到一个构造截断日期，如下所示:
SELECT cast(DATENAME(year, @Somedate) + '-' +
Convert(varchar(2),DATEPART(month, @Somedate)) + '-' +
DATENAME(day, @Somedate)
as datetime)
#12
0
Oracle:
Oracle:
TRUNC(SYSDATE, 'MONTH')
SQL Server:
SQL服务器:
DATEADD(DAY, - DATEPART(DAY, DateField) + 1, DateField)
Could be similarly used for truncating minutes or hours from a date.
也可以类似地用于截断日期或时间。
#13
-1
TRUNC(aDate, 'DD') will truncate the min, sec and hrs
TRUNC(aDate， 'DD')将截断最小，sec和hrs。
SRC: http://www.techonthenet.com/oracle/functions/trunc_date.php
SRC:http://www.techonthenet.com/oracle/functions/trunc_date.php
#1
412
This continues to frequently gather additional votes, even several years later, and so I need to update it for modern versions of Sql Server. For Sql Server 2008 and later, it's simple:
这将继续频繁地收集更多的选票，甚至几年之后，所以我需要更新Sql Server的现代版本。对于Sql Server 2008和稍后，它很简单:
cast(getDate() As Date)
Note that the last three paragraphs near the bottom still apply, and you often need to take a step back and find a way to avoid the cast in the first place.
请注意，底部的最后三段仍然适用，并且您经常需要后退一步，并找到一种方法来避免最初的转换。
But there are other ways to accomplish this, too. Here are the most common.
但也有其他的方法来实现这一点。这是最常见的。
The correct way (new since Sql Server 2008):
正确的方法(Sql Server 2008):
cast(getdate() As Date)
The correct way (old):
正确的方式(旧):
dateadd(dd, datediff(dd,0, getDate()), 0)
This is older now, but it's still worth knowing because it can also easily adapt for other time points, like the first moment of the month, minute, hour, or year.
现在已经变老了，但还是值得一看的，因为它也可以很容易地适应其他时间点，比如一个月、一分钟、一小时或一年的第一个时刻。
This correct way uses documented functions that are part of the ansi standard and are guaranteed to work, but it can be somewhat slower. It works by finding how many days there are from day 0 to the current day, and adding that many days back to day 0. It will work no matter how your datetime is stored and no matter what your locale is.
这种正确的方法使用了ansi标准的一部分的文档化函数，并保证可以工作，但是它可以稍微慢一些。它的工作原理是找出从第0天到现在的天数，并将其添加到第0天。无论您的datetime是如何存储的，无论您的语言环境是什么，它都可以工作。
The fast way:
快的方法:
cast(floor(cast(getdate() as float)) as datetime)
This works because datetime columns are stored as 8-byte binary values. Cast them to float, floor them to remove the fraction, and the time portion of the values are gone when you cast them back to datetime. It's all just bit shifting with no complicated logic and it's very fast.
这是因为datetime列被存储为8字节的二进制值。将它们转换为浮点数，让它们删除分数，当您将它们返回到datetime时，值的时间部分就消失了。这都是一点点变化，没有复杂的逻辑，而且速度非常快。
Be aware this relies on an implementation detail Microsoft is free to change at any time, even in an automatic service update. It's also not very portable. In practice, it's very unlikely this implementation will change any time soon, but it's still important to be aware of the danger if you choose to use it. And now that we have the option to cast as a date, it's rarely necessary.
请注意，这依赖于实现细节，微软可以随时更改，即使是在自动服务更新中。它也不是很便携。实际上，这种实现在短时间内不太可能发生变化，但是如果您选择使用它，了解它的危险仍然很重要。现在我们有了选择的日期，这是很少有必要的。
The wrong way:
错误的方式:
cast(convert(char(11), getdate(), 113) as datetime)
The wrong way works by converting to a string, truncating the string, and converting back to a datetime. It's wrong, for two reasons: 1)it might not work across all locales and 2) it's about the slowest possible way to do this... and not just a little; it's like an order of magnitude or two slower than the other options.
错误的方法是转换成字符串，截断字符串，然后转换回datetime。这是错误的，原因有二:1)它可能不适用于所有的地方，2)它是最慢的可能的方法…不仅仅是一点点;它就像一个数量级或者两个比其他选项慢。
Update This has been getting some votes lately, and so I want to add to it that since I posted this I've seen some pretty solid evidence that Sql Server will optimize away the performance difference between "correct" way and the "fast" way, meaning you should now favor the former.
最近更新了一些选票，所以我想补充一点，自从我发布这篇文章以来，我已经看到了一些非常可靠的证据，表明Sql Server将优化“正确”的方式和“快速”的方式之间的性能差异，这意味着您现在应该支持前者。
In either case, you want to write your queries to avoid the need to do this in the first place. It's very rare that you should do this work on the database.
在这两种情况下，您都希望编写您的查询，以避免在一开始就需要这样做。在数据库上做这项工作是非常罕见的。
In most places, the database is already your bottleneck. It's generally the server that's the most expensive to add hardware to for performance improvements and the hardest one to get those additions right (you have to balance disks with memory, for example). It's also the hardest to scale outward, both technically and from a business standpoint; it's much easier technically to add a web or application server than a database server and even if that were false you don't pay $20,000+ per server license for IIS or apache.
在大多数地方，数据库已经成为您的瓶颈。通常情况下，为性能改进添加硬件的成本最高，而最困难的是要获得这些附加功能(例如，您必须平衡磁盘与内存)。从技术上讲，从商业角度来说，这也是最难做到的。比起数据库服务器，在技术上添加web或应用服务器要简单得多，即使是错误的，也不需要为IIS或apache支付$20,000+的服务器许可证。
The point I'm trying to make is that whenever possible you should do this work at the application level. The only time you should ever find yourself truncating a datetime on Sql Server is when you need to group by the day, and even then you should probably have an extra column set up as a computed column, maintained at insert/update time, or maintained in application logic. Get this index-breaking, cpu-heavy work off your database.
我想说的是，只要有可能，就应该在应用程序级别进行这项工作。唯一一次在Sql Server上截断一个datetime的时间是当您需要按天分组时，即使这样，您可能还应该设置一个额外的列，作为计算列，在插入/更新时间维护，或在应用程序逻辑中维护。从你的数据库中获取这个不可分割的、cpu繁重的工作。
#2
43
For SQL Server 2008 only
仅适用于SQL Server 2008。
CAST(@SomeDateTime AS Date)
Then cast it back to datetime if you want
然后将它转换回datetime，如果您需要的话。
CAST(CAST(@SomeDateTime AS Date) As datetime)
#3
18
Just for the sake of a more complete answer, here's a working way for truncating to any of the date parts down and including minutes (replace GETDATE() with the date to truncate).
为了得到更完整的答案，这里有一个用于截断任何日期部分的工作方法，包括分钟(将GETDATE()替换为截断日期)。
This is different from the accepted answer in that you can use not only dd (days), but any of the date parts (see here):
这与接受的答案不同，你不仅可以使用dd (days)，还可以使用任何日期部分(见此处):
dateadd(minute, datediff(minute, 0, GETDATE()), 0)
Note that in the expression above, the 0 is a constant date on the beginning of a year (1900-01-01). If you need to truncate to smaller parts, such as to seconds or milliseconds, you need to take a constant date which is closer to the date to be truncated to avoid an overflow.
注意，在上面的表达式中，0是一年开始的常数日期(1900-01-01)。如果您需要截断到较小的部分，比如秒或毫秒，您需要使用一个更接近日期的固定日期，以避免溢出。
#4
7
The snippet I found on the web when I had to do this was:
我在网上找到的片段是:
dateadd(dd,0, datediff(dd,0, YOURDATE))
e.g.
dateadd(dd,0, datediff(dd,0, getDate()))
#5
1
In SQl 2005 your trunc_date function could be written like this.
在SQl 2005中，您的trunc_date函数可以写成这样。
(1)
(1)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
CAST(FLOOR( CAST( @date AS FLOAT ) )AS DATETIME)
END
The first method is much much cleaner. It uses only 3 method calls including the final CAST() and performs no string concatenation, which is an automatic plus. Furthermore, there are no huge type casts here. If you can imagine that Date/Time stamps can be represented, then converting from dates to numbers and back to dates is a fairly easy process.
第一种方法要干净得多。它只使用3个方法调用，包括final CAST()，并且不执行字符串连接，这是一个自动加值。此外，这里没有大型类型的类型转换。如果您可以想象日期/时间戳可以表示，那么从日期转换为数字和返回日期是一个相当简单的过程。
(2)
(2)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
SELECT CONVERT(varchar, @date,112)
END
If you are concerned about microsoft's implementation of datetimes (2) or (3) might be ok.
如果您对微软的datetimes(2)或(3)的实现感到担心，那就可以了。
(3)
(3)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
SELECT CAST((STR( YEAR( @date ) ) + '/' +STR( MONTH( @date ) ) + '/' +STR( DAY(@date ) )
) AS DATETIME
END
Third, the more verbose method. This requires breaking the date into its year, month, and day parts, putting them together in "yyyy/mm/dd" format, then casting that back to a date. This method involves 7 method calls including the final CAST(), not to mention string concatenation.
第三，更详细的方法。这就要求将日期划分为年、月和日部分，将它们放在“yyyy/mm/dd”格式中，然后将其转换为日期。这个方法包括7个方法调用，包括final CAST()，更不用说字符串连接了。
#6
1
CONVERT(DATE, <yourdatetime>) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)
#7
1
you could just do this (SQL 2008):
你可以这样做(SQL 2008):
declare @SomeDate date = getdate()
声明@SomeDate date = getdate()
select @SomeDate
2009-05-28
2009-05-28
#8
0
select cast(floor(cast(getdate() as float)) as datetime) Reference this: http://microsoftmiles.blogspot.com/2006/11/remove-time-from-datetime-in-sql-server.html
选择cast(层(cast)，如datetime)，引用如下:http://microsoftmiles.blogspot.com/2006/11/remove- datetime-in- datetime-in- datetime-in- time-in-sql-server.html。
#9
0
For those of you who came here looking for a way to truncate a DATETIME field to something less than a whole day, for example every minute, you can use this:
对于你们中来到这里的人，想要找到一种方法，将DATETIME字段截断为少于一整天的东西，例如，每分钟，你可以用这个:
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) + (FLOOR((CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 1440.0) + (3.0/86400000.0)) / 1440.0 AS DATETIME)
so if today was 2010-11-26 14:54:43.123 then this would return 2010-11-26 14:54:00.000.
因此，如果今天是2010-11-26,14:54:43.123，那么这将返回2010-11-26 14:54。
To change the interval it trucates to, replace 1440.0 with the number of intervals in a day, for example:
为了改变它的时间间隔，用一天的时间间隔替换1440.0，例如:
24hrs = 24.0 (for every hour)
24hrs / 0.5hrs = 48.0 (for every half hour)
24hrs / (1/60) = 1440.0 (for every minute)
(Always put a .0 on the end to implicitly cast to a float.)
(总是将.0放在末尾，隐式转换为浮点数。)
For those of you wondering what the (3.0/86400000) is for in my calculation, SQL Server 2005 doesn't seem to cast from FLOAT to DATETIME accurately, so this adds 3 milliseconds before flooring it.
对于那些想知道(3.0/8640000)在我的计算中是什么的人，SQL Server 2005似乎并没有精确地从浮点数转换到DATETIME，所以在使用它之前，它会增加3毫秒。
#10
0
This query should give you result equivalent to trunc(sysdate) in Oracle.
这个查询应该在Oracle中给出相当于trunc(sysdate)的结果。
SELECT *
FROM your_table
WHERE CONVERT(varchar(12), your_column_name, 101)
= CONVERT(varchar(12), GETDATE(), 101)
Hope this helps!
希望这可以帮助!
#11
0
You can also extract date using Substring from the datetime variable and casting back to datetime will ignore time part.
您还可以从datetime变量中使用子字符串提取日期，并将其转换回datetime将忽略时间部分。
declare @SomeDate datetime = '2009-05-28 16:30:22'
SELECT cast(substring(convert(varchar(12),@SomeDate,111),0,12) as Datetime)
Also, you can access parts of datetime variable and merge them to a construct truncated date, something like this:
此外，您还可以访问datetime变量的部分，并将它们合并到一个构造截断日期，如下所示:
SELECT cast(DATENAME(year, @Somedate) + '-' +
Convert(varchar(2),DATEPART(month, @Somedate)) + '-' +
DATENAME(day, @Somedate)
as datetime)
#12
0
Oracle:
Oracle:
TRUNC(SYSDATE, 'MONTH')
SQL Server:
SQL服务器:
DATEADD(DAY, - DATEPART(DAY, DateField) + 1, DateField)
Could be similarly used for truncating minutes or hours from a date.
也可以类似地用于截断日期或时间。
#13
-1
TRUNC(aDate, 'DD') will truncate the min, sec and hrs
TRUNC(aDate， 'DD')将截断最小，sec和hrs。
SRC: http://www.techonthenet.com/oracle/functions/trunc_date.php
SRC:http://www.techonthenet.com/oracle/functions/trunc_date.php