0

What is the default value for datetime2?

Edited: Sorry maybe I explain me wrong, I've tried to insert getDate() and it save me this value.

I need to know what is the value in this field (red one) of my ssms

EXAMPLE

Thom A
  • 88,727
  • 11
  • 45
  • 75
Mario Erro
  • 77
  • 1
  • 1
  • 7
  • You have to specify a column's default value! – jarlh Jan 24 '18 at 11:26
  • Post the code for your GETDATE() test. – Dan Guzman Jan 24 '18 at 11:46
  • The [Documentation](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql) lists the default value for each type. What this actually *means* however is often unclear. In this case, I believe it's the value you'll obtain if you try to cast or convert an *empty string* to `datetime2`. – Damien_The_Unbeliever Jan 24 '18 at 11:47
  • Your question is still unclear here. What do you mean it "saved you this value"? The box you've highlighted is the default value, it's not a stored value. Stored values are returned by using a `SELECT` statement. – Thom A Jan 24 '18 at 12:14

4 Answers4

6

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • The SQL Server documentation lists default values for many datatypes and the values listed aren't `NULL`. So whilst *you* may consider them to all have a default value of `NULL`, that's not what is documented. See e.g. link in comment to question. – Damien_The_Unbeliever Jan 24 '18 at 11:57
  • @Damien_The_Unbeliever Yes, that article is specifically talking about from other default values. For example, it states that if only a date is passed, the time portion will default to `'00:00:00'` or if only a time is passed, the date defaults to `'1900-01-01'`. If you have a table, with a `datetime2` then the default value will be what ever you specified. You can run the `CREATE` statement i provided above and test with that table if you wish to test. :) – Thom A Jan 24 '18 at 12:00
  • I've changed the image – Mario Erro Jan 24 '18 at 12:04
  • Instead of GetDate, you should use SYSDATETIME() – CaM Jan 04 '19 at 15:53
  • @CaM that's a preference thing. – Thom A Jan 04 '19 at 16:12
1

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

Leo
  • 398
  • 6
  • 11
1

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.

JimmyBytes
  • 57
  • 5
0

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 = ''

Rashmi Ranjan Ransingh
  • 51
  • 1
  • 5