SQL Server Date,datetime,datetime2 and time Brief introduction

SQL Server uses date to represent dates, time to represent times, and datetime and datetime2 to represent dates and times.

1, the precision of the second refers to the number of decimal places used to represent 1s:

The precision of the datetime data type seconds is 3,datetime2 and time can control the precision of the second,

The syntax is DateTime2 (n) and Time (n), and the value range for n is 0-7, and the default value is 7.

The 2,datetime data type stores the date and time, requires 8 bytes of fixed storage space, the default data format is YYYY-MM-DD hh:mm:ss.xxx, representing the date and time data from January 1, 1753 to December 31, 9999, The accuracy is 3.33 milliseconds or 0.00333 seconds, that is, the date range can be expressed from January 1, 1753 00:00:00.000 to December 31, 9999 23:59:59.997, accurate to 3.33 milliseconds.

Microsoft SQL Server Stores the value of a datetime data type internally with two 4-byte integers. The first 4 bytes stores the number of days before or after base date (that is, January 1, 1900). The base date is the system reference date. DateTime values older than January 1, 1753 are not allowed. The first 4 bytes: January 1, 1900 is 0, the previous date is a negative number, and the date is a positive number. Another 4-byte store represents the time of day as a 10/3-millisecond number.

Declare @dt datetime

Use GETDATE () and getUTCDate () to assign values to variables of the datetime type, the type of the return value of the two functions is a datetime

Declare @dt datetime Set @dt = getdate ()

The 3,datetime2 data type stores the date and time, and the storage space required is not fixed. Based on the stored time part fractional seconds precision to determine DateTime2 storage size,6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.

DateTime2 can represent a more accurate time than DateTime, the default data format is YYYY-MM-DD hh:mm:ss.nnnnnnn,datetime2 seconds the default precision is 7, that is, 7 decimal places to represent the precision of one second.

The syntax of DateTime2 is

[]

The following two ways to declare variables are equivalent.

Declare @dt2 datetime2 (7)declare@dt2 datetime2

To assign a value to a variable of type DateTime2, you need to use Sysdatetime () and Sysutcdatetime (), the type of the return value of the two functions is DateTime2

Declare @dt2 datetime2 Set @dt2 = Sysdatetime ()

The 4,date data type only stores dates, does not store time, requires 3B of storage space, the default data format is YYYY-MM-DD, and the supported date range is from 0001-01-01 to 9999-12-31

You can use date strings, the GETDATE () function, and the Sysdatetime () function to assign a value to a variable of date type

Declare @d Date Set @d = ' 2015-07-02 ' Set @d = getdate ()set@d=sysdatetime ()

The 5,time data type only stores time, does not store the date, and requires 5B of storage space.

The default fractional second precision is 7, and the default data format is hh:mm:ss.nnnnnnn.

Syntax for the time data type

[]

It is recommended that the time string and the Sysdatetime () function be used to assign a value to a variable of type. It is not recommended to use the GetDate () function, the GetDate () function returns a DateTime type, the time part of the fractional second precision does not have a time type high, and if the precision requirement for times is high, Use the time string and the Sysdatetime () function to assign a value to a variable of type.

-- Declare @t time (7) DECLARE @t  Time Set @t = ' 13:48:43.2840467 ' Set @t = sysdatetime () -- Not recommend Set @t = GETDATE ()

6,a Simple Example

Declare @dt2datetime2Declare @dDateDeclare @t Time--GetDate (), getUTCDate () the data type of the return value is a datetime--sysdatetime (), sysutcdate () the data type of the return value is DateTime2Select  @dt=getdate(),        @dt2=sysdatetime (),@d = Convert(nvarchar(8),getdate(), the),        @t='13:48:43.2840467'Select @dt  asDt@dt2  asDT2,@d  asD@t  asT

SQL Server Date,datetime,datetime2 and time Brief introduction