Datetime and smalldatetime
Represents the date and time data types of the date and the time within the day.
Microsoft SQL Server uses two 4-byte Integers to store values of the datetime data type. The number of days before or after the first 4-byte storage base date (January 1, 1900. The base date is the system reference date. Datetime values earlier than January 1, January 1, 1753 are not allowed. The first 4 Bytes: the value of the date on January 1, January 1, 1900 is 0, the date before is negative, and the date after is positive. The other 4-byte storage is represented by the daily time 3 1/3 milliseconds after midnight.
The smalldatetime data type stores the date and the daily time, but the accuracy is lower than datetime. SQL Server stores the smalldatetime value as two 2-byte integers. The first 2-byte is stored in the number of days after January 1, January 1, 1900. The other two bytes store the minute after midnight. The date range is from January 1, January 1-20, 1900 to January 1, June 6, accurate to minutes.
The two types can be converted to float floating point numbers. The integer part is the corresponding date byte, And the decimal part is the corresponding time ratio, for example, the decimal part of datetime is the percentage of milliseconds in the whole day; the decimal part of smalldatetime is the percentage of minutes in the whole day. therefore, we can directly add or subtract these two types of variables, integers, and floating-point numbers.
Datetime
From the date and time data of January 1, 3% to 3.33 seconds (equivalent to 0.00333 milliseconds or seconds ). As shown in the following table, adjust the value to an increment of. 000,. 003, or. 007 seconds.
| Example |
Adjusted example |
| 01/01/98 23:59:59. 999 |
00:00:00. 000 |
01/01/98 23:59:59. 995,
01/01/98 23:59:59. 996,
01/01/98 23:59:59. 997, or
01/01/98 23:59:59. 998 |
23:59:59. 997 |
01/01/98 23:59:59. 992,
01/01/98 23:59:59. 993,
01/01/98 23:59:59. 994 |
23:59:59. 993 |
01/01/98 23:59:59. 990 or
01/01/98 23:59:59. 991 |
23:59:59. 990 |
We often need to search for data within the specified date range. For example, if we return data within, you may write as follows:
Date> = '2014-01-01 00:00:00. 000' and date <= '2014-01-01 23:59:59. 100'
According to the above adjustment rules, the actual search range of this statement is:
Date> = '2014-01-01 00:00:00. 000' and date <= '2014-01-02 00:00:00. 000'
You will see that this includes data, so the best and correct search statement is:
Date> = '2014-01-01 00:00:00. 000' and date <'2014-01-02 00:00:00. 000'
Smalldatetime
The date and time data from January 1, January 1-20, 1900 to January 1, June 6 are accurate to minutes. The smalldatetime value of 29.998 s or lower is rounded down to the nearest minute, And the smalldatetime value of 29.999 s or higher is rounded up to the nearest minute.
-- Returns time as 12: 35
Select cast ('2017-05-08 12:35:29. 2000 'AS smalldatetime)
GO
-- Returns time as 12: 36
Select cast ('2017-05-08 12:35:29. 2000 'AS smalldatetime)
GO
Assignment:
The actual time format is described above. When we assign a value to a time variable, we certainly won't assign a floating point number to the variable. More often, we assign a string to the variable, the system automatically converts the string to the time format and saves it to the database. If the character format is incorrect, an error is returned. According to different languages, there are many different string representation methods in the world. We can use sp_helplanguage to view the default time formats in different languages. For example, the time format of Simplified Chinese is ymd, you can use set dateformat to temporarily change this default value.
Time Functions
DATEADD: You can add or subtract the specified part of the time type. Although we mentioned above, you can directly add or subtract the specified part, but we can more easily use this function to specify the part, such as year, month, day, hour, minute, and second. We often construct another time based on one time, such as today and the end of this month. We should also try to use the DATEADD function to construct it, it can avoid errors such as the leap month, the end of the year, and the end of the month. I used to construct them based on the DATENAME, and I often need to consider these issues.
DATEDIFF: This function compares two time variables to the specified part. This function does not consider the level of granularity higher than the specified date, but only the lower level. Use this function whenever possible for time comparison.
For example, if an error occurs, the time difference between the two time variables is returned. If you use DATEPART (HOUR, @ T2-@ T1), errors may occur. You should use DATEDIFF (HOUR, @ T1, @ T2 ).
DATEPART: return the value of the specified part of the time variable.
DATENAME: return the value of the specified part of the time variable. Different from DATAPART, this function returns a string type.
GETDATE () returns the current time of the machine. The CURRENT_TIMESTAMP variable has the same function as this function.
GETUTCDATE () returns the current UTC (Greenwich Mean Time) Time of the machine.
