Everything in SQL Server has a default value of NULL unless you specify otherwise. For example DECLARE @MyDate datetime2(0); : @MyDate will have a value of NULL.

As for tables:

CREATE TABLE #Sample (MyDate datetime2(0),
                      MyDate2 datetime2(0) DEFAULT GETDATE(),
                      MyDate3 datetime2(0) DEFAULT '20000101');

The column MyDate will have a default value of NULL, MyDate2 will be GETDATE() (which is the current date and time when the row was created). Finally MyDate3 will have a default value of 01 January 2000.

Stored Procedures/Functions work slight differently:

CREATE PROC ReturnDate @MyDate datetime2(0), @MyDate2 datetime2(0) = NULL, @MyDate3 = '20000101' AS...

Here, @MyDate has no default value, thus it must have a value supplied to be able to use the Procedure. @MyDate2 , however, has a default value of NULL, thus doesn't need to be supplied and the value NULL will be used. If a value for @MyDate2 is supplied, that supplied value will be used. @MyDate3 has a default value of 01 January 2000, thus if the parameter isn't specified then the default value will be used.

Edit: it's worth noting that if you pass NULL in an INSERT statement to a table, or SP/Function, with a default value (that isn't NULL ) then NULL will be used. It is only if the column/parameter is omitted from the INSERT / EXEC /etc statement that the default value will be used.

If you need to specify the default value you should use that format : '9999-12-31 23:59:59.9999999'

If you need UTC Now, you should use getutcdate()

https://i.stack.imgur.com/a3OiS.png

In the Microsoft Documentation for datetime2 , it shows that the default value for datetime2 is

'1900-01-01 00:00:00'

It's four years late, but I hope it helps others down the line.

The default value is '1900-01-01 00:00:00.0000000' in SQL Server but when you try from EntityFramework it comes '0001-01-01 00:00:00.0000000' and to achieve this you put your code like this :

the datetime variable or value should be assigned ''

@AssetCode nvarchar(100)='',  
@AssignedToDept nvarchar(100)='',
@AssignedToDeptDate datetime2(7)='',
@AssignedToOwnerDate datetime2(7)='',
@CreatedBy nvarchar(100)='',
@CreatedDate datetime2(7)='',
@CreatedMode nvarchar(100)='',
@Description nvarchar(max)='',
@IsClosed bit=false,
@IsDeleted bit=false,
@IsDeletedBy nvarchar(100)='',
@OwnerId nvarchar(100)='',
@PersonEmail nvarchar(100)='',
@PersonName nvarchar(100)='',
@PersonPhone nvarchar(100)='',
@Priority int=4,
@Subject nvarchar(100)='',
@TicketNumber nvarchar(450)='',
@TicketStatus nvarchar(100)='',
@TicketType nvarchar(100)='',
@UpdatedDate datetime2(7)='',
@UpdatedBy nvarchar(100)=''

set @TicketNumber='IN00000361'
INSERT INTO [dbo].[TblTickets]([TicketNumber], [TicketType], [Subject], 
[Description], [Priority], [CreatedDate], [CreatedBy], [CreatedMode], [PersonPhone], 
[PersonName], [PersonEmail], [UpdatedDate], [UpdatedBy], [AssignedToDept], 
[AssignedToDeptDate], 
[OwnerId], [AssignedToOwnerDate], [IsClosed], [IsDeleted], [IsDeletedBy], 
[TicketStatus], [AssetCode])
VALUES( @TicketNumber, @TicketType, @Subject, @Description, @Priority, 
@CreatedDate, @CreatedBy, @CreatedMode, @PersonPhone, @PersonName, 
@PersonEmail, @UpdatedDate, @UpdatedBy, @AssignedToDept, @AssignedToDeptDate,
@OwnerId, @AssignedToOwnerDate, @IsClosed, @IsDeleted, 
@IsDeletedBy, @TicketStatus, @AssetCode)

So the output be like this

Id  TicketNumber    TicketType  Subject Description Priority    CreatedDate CreatedBy
CreatedMode PersonPhone PersonName  PersonEmail UpdatedDate UpdatedBy   AssignedToDept
AssignedToDeptDate  OwnerId AssignedToOwnerDate IsClosed    IsDeleted
IsDeletedBy TicketStatus    AssetCode
24  IN00000361              4   1900-01-01 00:00:00.0000000
1900-01-01 00:00:00.0000000         1900-01-01 00:00:00.0000000
1900-01-01 00:00:00.0000000 0   0

Also when you look up for your record the query is select * from TblTickets where UpdatedDate = ''