This article contains examples of converting a datetimeoffset value to datetime2 in SQL Server.
When you convert a datetimeoffset value to datetime2, the date and time are copied to the datetime2 value, and the time zone is truncated. The fractional seconds are also truncated to fit if the destination precision is lower.
This article contains examples of converting a datetimeoffset value to datetime in SQL Server.
When you convert a datetimeoffset value to datetime, the date and time values are copied, and the time zone offset is truncated. When the fractional precision of the datetimeoffset value is greater than three digits, the value is truncated.
This article contains examples of converting a smalldatetime value to a time value in SQL Server.
When you convert a smalldatetime value to time, you lose the date. The hours, minutes, and seconds are copied. The fractional seconds are set to 0.
This article contains examples of converting a smalldatetime value to a date value in SQL Server.
One of the benefits of converting a smalldatetime value to date is that you reduce the storage size down from 4 bytes to 3 bytes. However, you do lose the time component from the value, so you would only do this conversion if you don’t need the time.
This article contains examples of converting a smalldatetime value to a datetime value in SQL Server.
The smalldatetime data type doesn’t have any fractional seconds, and its seconds component is always set to zero (:00). Its accuracy is to the nearest minute.
The datetime data type on the other hand, includes a 3-digit fractional seconds part, and its accuracy is rounded to increments of .000, .003, or .007 seconds.
When you convert a smalldatetime value to datetime, the smalldatetime value is copied to the datetime value. The fractional seconds are set to 0.
In most cases, you’d be better off converting to a datetime2 data type instead of a datetime. Doing this will provide increased accuracy, while using the same storage size. However, if you really do need it to be datetime, here are some examples.
This article contains examples of converting a smalldatetime value to a datetimeoffset value in SQL Server.
When you convert a smalldatetime value to datetimeoffset, the smalldatetime value is copied to the datetimeoffset value. The fractional seconds are set to 0, and the time zone offset is set to +00:0.
This article contains examples of converting a smalldatetime value to a datetime2 value in SQL Server.
The smalldatetime data type doesn’t have any fractional seconds, and its seconds component is always set to zero (:00). Its accuracy is to the nearest minute.
The datetime2 data type on the other hand, allows you to specify a fractional seconds precision from 0 to 7. If you don’t specify this, it will use 7 (the default). If you specify zero (0), its accuracy will be to the nearest second.
When you convert a smalldatetime value to datetime2, the hours and minutes are copied. The seconds and fractional seconds are set to 0.
This article contains examples of converting a datetime2 value to a smalldatetime value in SQL Server.
One of the benefits of converting a datetime2 value to smalldatetime is that you reduce the storage size from either 7, 8 or 9 bytes down to 4 bytes. However, you do lose precision.
There are probably very few use cases that would cause you to convert a datetime2 value to datetime in SQL Server. In particular, the datetime2 data type can be set to use the same storage size as datetime, but with higher precision. So in most cases you’d be better off with datetime2 than with a datetime. Microsoft also recommends using datetime2 instead of datetime.
However, in case you find yourself in the situation where you need to perform this conversion, this article contains some examples and considerations that may be of assistance.
This article contains examples of converting a datetime2 value to a datetimeoffset value in SQL Server.
When you convert a datetime2 value to datetimeoffset, the resulting value will depend on the fractional seconds precision that’s assigned to each data type, as well as any time zone offset you specify.
Both data types allow you to specify a fractional seconds precision from 0 to 7. If you don’t specify this, the default scale of 7 is used.
The datetimeoffset data type includes a time zone offset and can preserve any offsets in the original value. However, datetime2 has no time zone awareness, so there are no existing values to preserve. In this case, the time zone offset defaults to +00:00.
The TODATETIMEOFFSET() function was specifically designed to convert a date/time value to datetimeoffset and add a time zone offset. However, see my comments (and examples) below regarding this option.