本文介绍了在 SQL Server 中设置日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在 SQL Server 中，如何将 DATETIME 设置为秒/分钟/小时/日/年?
In SQL Server, how do I "floor" a DATETIME to the second/minute/hour/day/year?
假设我的日期是2008-09-17 12:56:53.430，那么地板的输出应该是:
Let's say that I have a date of 2008-09-17 12:56:53.430, then the output of flooring should be:
- 年份:2008-01-01 00:00:00.000
- 月份:2008-09-01 00:00:00.000
- 日期:2008-09-17 00:00:00.000
- 时间:2008-09-17 12:00:00.000
- 分钟:2008-09-17 12:56:00.000
- 第二次:2008-09-17 12:56:53.000
推荐答案
关键是要使用DATEADD 和 DATEDIFF 以及相应的 SQL时间跨度枚举.
The key is to use DATEADD and DATEDIFF along with the appropriate SQL timespan enumeration.
declare @datetime datetime;
set @datetime = getdate();
select @datetime;
select dateadd(year,datediff(year,0,@datetime),0);
select dateadd(month,datediff(month,0,@datetime),0);
select dateadd(day,datediff(day,0,@datetime),0);
select dateadd(hour,datediff(hour,0,@datetime),0);
select dateadd(minute,datediff(minute,0,@datetime),0);
select dateadd(second,datediff(second,'2000-01-01',@datetime),'2000-01-01');
select dateadd(week,datediff(week,0,@datetime),-1); --Beginning of week is Sunday
select dateadd(week,datediff(week,0,@datetime),0); --Beginning of week is Monday
请注意，当您按秒计算时，如果使用 0，通常会出现算术溢出.因此请选择一个已知值，该值保证低于您尝试计算的日期时间.
Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor.
这篇关于在 SQL Server 中设置日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！