一种在SQL Server中无秒数存储时间的方法

I want to save time in my SQL Server database in this format:

我希望以这种格式在SQL Server数据库中节省时间:

hh:mm

and not this:

而不是这个:

hh:mm:ss

Is that possible? Is there a unique data type for that (I tried to go over the list of data types but failed to find; tried also to search on MSDN and didn't find). Is there another way? I really do not want the second to be stored.

那可能吗?是否有一个独特的数据类型(我试图浏览数据类型列表但找不到;尝试也搜索MSDN,但没有找到)。还有另一种方式吗?我真的不希望存储第二个。

6 个解决方案

#1

If you're using SQL-Server 2008+ , you can use the TIME type and just reset the seconds and just ignore them while selecting.

如果您使用的是SQL-Server 2008+,则可以使用TIME类型,只需重置秒,然后在选择时忽略它们。

If not, you can consider saving them as strings(extract the HH:MI) or integers(counting minutes after 00:00). Like @destination-data said, you should note that you won't be able to use the time functions available for SQL-Server, but you will be able to convert them back to a date if necessary .

如果没有,您可以考虑将它们保存为字符串(提取HH:MI)或整数(在00:00之后计算分钟数)。就像@destion-data所说的那样,你应该注意到你将无法使用SQL-Server可用的时间函数,但如果需要,你可以将它们转换回日期。

#2

I think when you need only hour and minute use two columns for each one instead like this:

我想当你只需要每小时和每分钟使用两列时,就像这样:

Hour tinyint
Minute tinyint

#3

No there isn't a data type for this. The smallest is time(0), which is accurate to one second.

不,没有这种数据类型。最小的是时间(0),精确到一秒。

SELECT
    -- Returns time in the format hh:mm:ss
    CAST(GETDATE() AS TIME(0))
;

You could add a check constraint to ensure the seconds are always equal to 00, but I'm not sure what value this would add.

你可以添加一个检查约束来确保秒总是等于00,但我不确定它会添加什么值。

#4

Use Format function available from SQL Server 2012

使用SQL Server 2012提供的格式功能

select format(getdate(),'hh:mm')

Insert into sometable
(timepart)
values
(
format(getdate(),'hh:mm'))

Output:

07:08

#5

It is always stored the only thing you can do is to edit the value before it gets posted so the seconds (and also the milliseconds which are also stored) are set to 0.

始终存储您唯一能做的就是在发布之前编辑该值,以便将秒(以及也存储的毫秒数)设置为0。

I do this in my c# application where all inserts end updates are passing through one routine that checks for datetime datatype and then filters out the milliseconds.

我在我的c#应用程序中执行此操作,其中所有插入结束更新都通过一个检查datetime数据类型的例程,然后过滤掉毫秒。

#6

Try this:

SELECT FORMAT(GETDATE(),'HH:mm')

#1