Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the role of datetime and smalldatetime types in SQLSERVER, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Datetime and smalldatetime represent the date and time data types of the date and the time of day. Microsoft SQL Server stores values of the 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 earlier than January 1, 1753 are not allowed. The first 4 bytes: January 1, 1900, is 0; the previous date is negative; the subsequent date is positive. Another 4-byte stores the time of day represented by the number of 3 1 seconds after midnight. The smalldatetime data type stores the date and time of day, but with less accuracy than datetime. SQL Server stores the value of smalldatetime as two 2-byte integers. The first 2 bytes stores the number of days after January 1, 1900. The other 2 bytes stores the number of minutes after midnight. Dates range from January 1, 1900 to June 6, 2079, accurate to minutes. These two types can be converted into float floating point numbers, in which the integer part is the corresponding date byte, and the decimal part is the corresponding proportion of time, for example, the decimal part of datetime is the proportion of milliseconds of the whole day; the decimal part of smalldatetime is the proportion of minutes of the whole day. Therefore, we can directly add and subtract these two types of variables, integers and floating-point numbers. Datetime date and time data from January 1, 1753 to December 31, 9999, with an accuracy of 3% seconds (equal to 3.33ms or 0.00333 seconds). Adjust the value to an increment of .000, .003, or .007 seconds as shown in the following table. Example

Adjusted example

01/01/98 23:59:59.999

1998-01-02 0000 purl 00.000

01Accord01According to 01Action01According to the fact that there is no way to do so, we may not be able to do so. 01According to 01Universe, 01According to 99.995jpg 59.995j01Gram59.9996, 01Accord01According to 98 23VERD 59.996,01Univer 599997, or 01Compare 01According to 01Accord, 98 2359

1998-01-01 23 Suzhou 599.997

01/01/98 23:59:59.992, 01/01/98 23:59:59.993, 01/01/98 23:59:59.994

1998-01-01 23 Suzhou 599.993

01Accord 01Compact 98 23purl 59purl 59.990 or 01Unipex 01Universe 98 23Groupe 59purl 59.991

1998-01-01 23 Suzhou 599.990

We often need to search for data within a specified range of dates, such as returning data from 1998-01-01. You might write: date > = '1998-01-01 00 and date 00.000' and date = '1998-01-01 00 smalldatetime date and time data from January 1, 1900 to June 6, 2079 are accurate to minutes. Smalldatetime values of 29.998 seconds or less are rounded down to the nearest minute, and smalldatetime values of 29.999 seconds or higher are rounded up to the nearest minute. -- returns time as 12:35 SELECT CAST ('2000-05-08 12 AS smalldatetime) GO-- returns time as 12:36 SELECT CAST (' 2000-05-08 12 GO 35 GO 29.999'AS smalldatetime) GO assignment: the actual format of time is mentioned above. When we assign a value to a time variable, we certainly will not assign a floating point number to the variable. More often, we assign a string to the variable. The system automatically changes the string to a time format and saves it to the database. If the character format is wrong, an error will be reported. Depending on the language, there are many different ways to express time in strings in the world. We can view the default time format in different languages through sp_helplanguage, such as ymd in simplified Chinese. We can change this default value temporarily through SET DATEFORMAT. Time function DATEADD: you can add and subtract the specified part of the time type. Although we said above that we can add and subtract directly, we can more easily use this function to add and subtract the specified part, such as year, month, day, hour, second, etc. We often construct another time based on one time, such as today of next month, the end of this month, and so on. We should also try to use the DATEADD function to construct it, which can avoid some errors such as leap month, end of year, end of month and so on. I used to construct based on DATENAME, and I often have to consider these problems. DATEDIFF: this function compares and calculates the specified part of two time variables. This function does not consider a higher level of granularity than the specified date part, it only considers lower-level parts. This function should be used as far as possible for time comparison. An example of an error: returns the hour difference between two time variables. Errors may occur if you use DATEPART (HOUR, @ T2-@ T1); you should use DATEDIFF (HOUR, @ T1, @ T2). DATEPART: returns the value of the specified part of the time variable. DATENAME: returns the value of the specified part of the time variable. Unlike DATAPART, this function returns a string type GETDATE () that 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.

After reading the above, do you have any further understanding of the role of datetime and smalldatetime types in SQLSERVER? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.