Can SQL Server 2008 TIME accuracy be reduced to hours and minutes?

Question

In SQL Server 2008, the time data type has an optional precision argument (default is 7). In this case, you can control how many fractional decimal places are stored and displayed.

 DECLARE @time time(3) SET @time = GETDATE() PRINT @time 

The above will print this,

 10:47:25.347 

The documentation indicates the least precision time(0) . This will save and print 10:47:25 .

Is it possible to further reduce accuracy to exclude / disable seconds: 10:47 ?

I know this can be done manually by adding a constraint ( DATEPART(seconds, @time) = 0 ), doing the math when entering data to zero seconds and manually formatting when printing, but I'm looking for an easier way to simply define the field in the table as " hours and minutes, "in much the same way as the date type allows you to define a field as" just a date, not a time. "

Answer 1

Not. It is not possible to reduce the accuracy of the time data type to time(0)

Answer 2

Use smalldatetime and ignore the date bit (this will be January 01, 1900). It has a little accuracy.

As time as it has been done these days (SQL Server 2005 and earlier :-)

Answer 3

 SET @time = convert(varchar(5),getdate(),8) 

Here you pass the GETDATE result to VARCHAR and form it using a modifier , which means a time format.

EDIT: This gives you a line, maybe this is not the result you are looking for.

Sincerely.

Answer 4

If the desired interval is one of the recognized SQL Server intervals (Minute, Hour, Day, etc.), then another option you may consider is that you define your own data type (for example, you want a clock) and then store it as an integer, which is just an absolute hour number starting from some base time count (or use the same time count base that SQL Server uses (at midnight January 1, 1900)

Convert from a stored integer value ( @IntVal ) to the actual hour as a date and time ( @realDT ) using the following. (assuming @baseDatetime is defined as base time

 Declare @baseDatetime smalldatetime Set @baseDatetime = 0 -- for 1 Jan 1900, or Set @baseDatetime = 'd Month yyyy' for some other base @realDT->@IntVal: @IntVal = DateDiff(hour, @baseDatetime, @realDT) @IntVal->@realDT: @realDT = DateAdd(hour, @IntVal, @baseDatetime)