How to set a default value on ' Time' datatype

Question

Hello.

My details here below. (I use SQL Server 2019 Express on Windows 10)

SQL Server Management Studio 15.0.18338.0
SQL Server Management Objects (SMO) 16.100.41011.9
Microsoft Analysis Services Client Tools 15.0.19205.0
Microsoft Data Access Components (MDAC) 10.0.17763.1
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.17763

My target is to set a default value to the time field
i was able with the date, but applying the same philosophy using default , here gives a error instead

Code:

alter table test
alter column test13 time(3) default '00:00:000'
select * from test

Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'default'.

Completion time: 2020-11-03T15:58:33.3610254+00:00

Any idea or suggestion to fix is welcome. Thanks in advance.

Paolo

Answer 1

Please check the following example.

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.test;

CREATE TABLE dbo.test (ID INT, test13 TIME(3));

ALTER TABLE dbo.test 
ADD CONSTRAINT DF_SomeName DEFAULT N'00:00:000' FOR test13;

Answer 2

Hello.

I have resolved with this code

ALTER TABLE TEST

ADD CONSTRAINT dF_TIME DEFAULT'00:00:00' FOR TEST13

Paolo