DateTime and smalldatetime
represents the date and time data type for the date and time of day.
Microsoft SQL Server stores values for datetime data types within two 4-byte integers. The first 4 bytes stores the number of days before or after base date (i.e. January 1, 1900). The base date is the system reference date. DateTime values older than January 1, 1753 are not allowed. First 4 bytes: January 1, 1900 is 0; the date before is a negative number, and then the date is a positive number. Another 4-byte store is the daily time represented by the 3 1/3 milliseconds after midnight. The
smalldatetime data type stores the date and time of day, but is less accurate than DateTime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. Another 2 byte stores the number of minutes after midnight. Date range from January 1, 1900 to June 6, 2079, accurate to minutes. The
can convert these two types to float floating-point numbers, where the integer part is the corresponding date byte, and the decimal part is the corresponding proportion of the time, such as the fractional portion of the datetime that is the proportion of the number of milliseconds in the day; smalldatetime The fractional part of the day is the proportion of minutes. So we can directly add and subtract these two types of variables and integers and floating-point numbers.
DateTime
Date and time data from January 1, 1753 through December 31, 9999 with an accuracy of 3% seconds (equal to 3.33 milliseconds or 0.00333 seconds). Adjust the value to the increment of. 000,. 003, or. 007 seconds, as shown in the following table.
| Sample |
an adjusted example |
| 01/01/98 23:59:59.999 |
1998-01-02 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 |
1998-01-01 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 |
1998-01-01 23:59:59.993 |
01/01/98 23:59:59.990 or
01/01/98 23:59:59.991 |
1998-01-01 23:59:59.990 |
We often need to search for data within a given date range, such as returning data from the 1998-01-01 day, as you might write:
Date >= ' 1998-01-01 00:00:00.000 ' and date <= ' 1998-01-01 23:59:59.999 '
According to the above adjustment rule, actually this sentence's actual search scope is:
Date >= ' 1998-01-01 00:00:00.000 ' and date <= ' 1998-01-02 00:00:00.000 '
You will see that this includes the 1998-01-02 data, so the best correct search statement is:
Date >= ' 1998-01-01 00:00:00.000 ' and date < ' 1998-01-02 00:00:00.000 '
smalldatetime
Date and time data from January 1, 1900 to June 6, 2079 are accurate to minutes. The smalldatetime value of 29.998 seconds or less is rounded down to the nearest minute, and the smalldatetime value of 29.999 seconds or higher is rounded up to the nearest minute.
--returns time as 12:35
SELECT CAST (' 2000-05-08 12:35:29.998 ' as smalldatetime)
Go
--returns time as 12:36
SELECT CAST (' 2000-05-08 12:35:29.999 ' as smalldatetime)
Go
Assign value:
It says the actual format of time, we will definitely not assign a floating-point number to the variable when assigning a value to a time variable, more often we assign a string to the variable, and the system automatically turns the string into a time format and saves it to the database. If the character format is wrong, the error occurs. Depending on the language, there are a number of different times in the world, we can view the default time format in different languages by sp_helplanguage, such as the time format of Simplified Chinese ymd, you can temporarily change this default value through SET DateFormat.
Time function
DATEADD: You can add and subtract the specified parts of the type of time, although we say that we can make direct addition and subtraction, but we can make it easier to use this function for the specified part, such as the date and time of the month and so on to add and subtract. We often based on a time to construct another time, such as the next month today, the end of this month, and so on, we should also try to use the DATEADD function to construct, it can avoid some of the leap month, end of the month, such as the error, I used to be based on datename to construct, often
DATEDIFF: This function compares the specified part to two time variables. This function does not consider a level of granularity that is higher than the specified date part, and it considers only the lower-level portions. You should use this function as much as possible in comparison to time.
Example of an error: A small time difference that returns two variables. If you use DATEPART (HOUR, @T2-@T1) you can make mistakes; 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 () returns the current time for this machine. The CURRENT_TIMESTAMP variable has the same function as this function.
getUTCDate () returns the current UTC (Greenwich Mean Time) time for this machine.
