How to output DATEADD with a specific precision?

Question

I have a procedure which is called from external application. The procedure has a parameter time of bigint type. In the procedure I insert the time to my table to column of 'time' type. To convert bigint to time I use DATEADD:

DATEADD(S, @piHMSTime, '19700101')

Everything works fine but I get time in my table like this 22:59:57.0000000. How do I set a needed precision? I need only seconds or maybe 2 digits after point.

Gordon Linoff · Answer 1 · 2019-05-26T02:57:33.870

0

Don't worry about it. The value you are storing uses an internal format.

If you only want to see the time up to seconds, use convert():

convert(varchar(8), timecol, 114)

If the column is defined as time, then the default is to have seven digits of precision for seconds (100 nanoseconds). If you only want seconds, use time(0):

alter table t alter column timecol time(0);

edited May 26 '19 at 02:57

answered May 26 '19 at 02:46

Gordon Linoff

1,242,037
58
646
786

[Documentation](https://learn.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql?view=sql-server-2017) says the default is seven not six. – sticky bit May 26 '19 at 02:52

score 0 · Answer 2 · answered May 26 '19 at 03:27

The external application is sending you the number of seconds since 1970-01-01. As long as that doesn't change you can't get more precision.

If you can get the external application to pass you milliseconds since 1970-01-01, you can use the function shown here.

How to output DATEADD with a specific precision?

2 Answers2