Setting the Default Value or Binding for time data type

Other SQL Server Topics
1

Hi,
I am new to Sql Server.

I want to create a table with time datatype column.

But I could not set the default fractional seconds to 3 fractional seconds eg. 00:00:09.222

It always shows as 00:00:09.2220000

Is it possible to set the Dafault Value or Binding to 3 fractional seconds as above.

Thanks

2

Create the column with a data type of time(3).

3

I tried. Not working

image

4

This works so it must be something to do with the editor you are using:

USE tempdb;
GO
DROP TABLE IF EXISTS dbo.test;
CREATE TABLE dbo.test
(
	TestTime time(3) DEFAULT(SYSDATETIME()) NOT NULL
);
INSERT INTO dbo.test VALUES(DEFAULT);
SELECT * FROM dbo.test;
DROP TABLE dbo.test;
GO
5

Thanks.

But instead of DEFAULT(SYSDATETIME())

can we use something like DEFAULT("00:00:00.000") since I want to set the default timing as "00:00:00.000"

6

If you want a default you can use whatever you want. In a real table I would specify a constraint name istead of relying on a system generated one. eg:

TestTime time(3) CONSTRAINT dbo_test_TestTime_DF DEFAULT('0:0') NOT NULL