SQL DATETIME Vs SMALLDATETIME Datatypes
The main differences between SQL Server's DATETIME
and SMALL DATETIME
data types.
Although both data types are used to store date and time values, there are some distinctions between them.
In most circumstances, it's advisable to avoid both types and instead use DATETIME2
(Microsoft also recommends this).
In any case, here's how these two data types compare.
SQL DATETIME Datatype
The DATETIME data type is created by converting date and time data types.
The year, month, and day are copied when converting from date. 00:00:00.000 is the time component's value.
Example: The results of converting a date value to a datetime value are shown in the code below.
Sqlserver
DECLARE @date date = '24-03-2017';
DECLARE @datetime datetime = 24-03-2017;
SELECT @datetime AS 'Datetime', @date AS '@date';
The above statement we first pass date value to date datatype then datetime datatype
Output: The result of datetime will be
Datetime | Date |
---|---|
24-03-2017 00:00:00:000 | 24-03-2017 |
SQL SMALLDATETIME DATATYPE
The date and time data types of the SMALLDATETIME data type are translated to other date and time data types.
The year, month, and day are copied for a date conversion.
Example: The results of converting a SMALLDATETIME value to a date value are shown in the code below.
Sqlserver
DECLARE @smalldatetime smalldatetime = '1940-07-13 12:20:30
DECLARE @date date = '1940-12-13';
SELECT @smalldatetime AS 'Smalldatetime', @date AS 'Date';
This function have limited value to store.In this example we cannote the value Less than year of 1900 & greater than 9999.
Result: The output will be
SmallDatetime | Datetime |
---|---|
1940-07-13 | 1940-12-13 |
The hours, minutes, and seconds are replicated when converting to time(n).
The fractional seconds have been set to 0 by default.
The results of converting a smalldatetime value to a time(4) value are shown in the following code.
SQL Difference Between DATETIME And SMALL DATETIME Datatype
Below table contains contrast in datetime vs small datetime:-
DATETIME | SMALL DATETIME |
---|---|
If you need precision to the second (or even fractional seconds), datetime is the way to go. | If you don't need second-by-second accuracy, smalldatetime will suffice while using up half the storage capacity. |
Character Length - Minimum 19 , Maximum 23 | Character Length - Maximum 19/td> |
SQL Compliant (ANSI & ISO 8601) No | SQL Compliant (ANSI & ISO 8601) No |
FORMAT - YYYY-MM-DD hh:mm:ss.nnn | FORMAT -YYYY-MM-DD hh:mm:ss |
Date Range MIN Value - 1753-01-01 00:00:00 | Date Range MIN Value - 900-01-01 00:00:00 |
Date Range MAX Value -2079-06-06 23:59:00 | Date Range MAX Value - 9999-12-31 23:59:59.997 |
Storage Size - 8 bytes | Storage Size - 4 bytes,fixed size |
Accuracy - Rounded to increments of .000, .003, or .007 second It means: If time part in the date is 23:59:58.990 or 23:59:58.991, it will be stored as 23:59:58.990. | Accuracy - 1 Minute Second’s values that are 29.998 seconds or less are rounded down to the nearest minute. And second’s values of 29.999 seconds or more are rounded up to the nearest minute. So seconds part value is always 00. |
Time zone offset aware and preservation - No | Time zone offset aware and preservation - No |
Time Zone Offset - None | Time Zone Offset - None |
Fractional second precision - Yes | Fractional second precision - No |
User-defined fractional second precision - No | User-defined fractional second precision - No |
Daylight saving aware - No | Daylight saving aware - No |