SQL Server 2008 TIME的精度可以降低到只有小时和分钟吗?
在 SQL Server 2008 中,time
数据类型具有可选的精度参数(默认值为 7)。通过这个,您可以控制存储和显示多少个小数位。
DECLARE @time time(3)
SET @time = GETDATE()
PRINT @time
上面会打印这个,
10:47:25.347
文档说最小精度是time(0)
。这将存储并打印 10:47:25
。
是否可以进一步降低精度,以消除/清零秒:10:47
?
我知道这可以通过添加约束(DATEPART(seconds, @time) = 0
)、对数据输入执行数学运算以将秒数归零以及在打印时手动格式化来手动完成,但我是寻找一种更简单的方法来将表中的字段定义为“小时和分钟”,与 date
类型允许您将字段定义为“只有日期,没有时间”的方式大致相同成分”。
With SQL Server 2008 the time
data type has an optional precision argument (default is 7). With this you can control how many fractional decimal places are stored and displayed.
DECLARE @time time(3)
SET @time = GETDATE()
PRINT @time
The above would print this,
10:47:25.347
The documentation says the smallest precision is time(0)
. This would store and print 10:47:25
.
Is it possible to reduce the precision even more, to eliminate/zero out seconds: 10:47
?
I know this can be done manually by adding a constraint (DATEPART(seconds, @time) = 0
), performing math on data entry to zero out the seconds, and manually format when printing, but I am looking for a simplier way to just define a field in a table as "hours and minutes", in much the same way that the date
type allows you to define a field as "just the date, no time component".
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
不可以。
time
数据类型的精度不可能降低到超过time(0)
No. It is not possible to reduce the precision of the
time
datatype any further thantime(0)
使用smalldatetime 并忽略日期位(它将是1900 年1 月1 日)。它具有微小的精度。
尽管时间很有用,但这就是我们过去的做法(SQL Server 2005 及之前:-)
Use smalldatetime and ignore the date bit (it will be 01 Jan 1900). It has minute accuracy.
As useful as time is, this is how we did in the olden days (SQL Server 2005 and before :-)
在这里,您将 GETDATE 的结果转换为 VARCHAR 并使用修饰符8表示时间格式。
编辑:这给了你一个字符串,也许不是你正在寻找的结果。
问候。
Here you are casting the result of GETDATE to a VARCHAR and formating it with the modifier 8 that means time format.
EDIT: This is giving you a string, maybe is not the result you are looking for.
Regards.
如果您想要的间隔是 SQL Server 识别的间隔之一(分钟、小时、天等),那么您可能会考虑的另一个选择是定义自己的数据类型(假设您想要小时),然后将其存储为一个整数,它只是一个绝对小时数,从某个基准参考时间开始(或使用 SQL Server 使用的相同基准参考时间(1900 年 1 月 1 日午夜)
)使用以下命令将存储的整数值 (
@IntVal
) 转换为日期时间形式的实际小时 (@realDT
)(假设@baseDatetime
)。 > 定义为基准时间If the interval you want is one of the SQL Server recognized intervals (Minute, Hour, Day, etc... then another option you might consider, is that you define your own datatype, (say you want hours) and then store it as an integer which is just an absolute hour number, starting from some base reference time (or use the same base reference time that SQL Server uses (midnight 1 Jan 1900)
Convert from the stored integer value, (
@IntVal
), to the actual hour as a datetime, (@realDT
)using the following. (assuming@baseDatetime
is defined as the base time如果你的存储空间有问题,使用 CHAR(5) 只会增加问题,使用 DATALENGTH 来检查:
考虑到 CHAR(5) 使用与 TIME(7) 相同的空间,为什么不使用 TIME( 7)?
更好的选择是使用 TIME(2) 或使用 计算列 和一个smallint 列,仅存储分钟和时间:
虽然它使用较少的存储空间,但如果其他进程正在使用该字段,因为计算列的“隐式”日期添加和转换会非常慢
If you are having a problem with storage space, using CHAR(5) will only increase the issue, use DATALENGTH to check:
Considering that CHAR(5) uses the same space of TIME(7), why won't you use TIME(7)?
A better option is to use TIME(2) or using a computed column and a smallint column to store only minutes and time:
Although it uses less storage space it will cost to the CPU, if other processes are using the field as time they are going to be really slow because of the dateadd and conversion "implicit" of the computed column