When you convert between date and time data types in SQL Server, you need to be mindful of how the new data type will handle the value you’re trying to assign to it.
In some cases you might lose part of the value, in other cases you might gain a bunch of zeros (increasing storage size in the process). You may also end up with a value that’s been rounded up.
The following articles contain examples of conversions between the different date and time data types, with a particular focus on the issues I just mentioned.
Convert ‘datetime’ to …
- Convert ‘datetime’ to ‘datetime2’
- Convert ‘datetime’ to ‘datetimeoffset’
- Convert ‘datetime’ to ‘smalldatetime’
- Convert ‘datetime’ to ‘date’
- Convert ‘datetime’ to ‘time’
Convert ‘datetime2’ to …
- Convert ‘datetime2’ to ‘datetime’
- Convert ‘datetime2’ to ‘datetimeoffset’
- Convert ‘datetime2’ to ‘smalldatetime’
- Convert ‘datetime2’ to ‘date’
- Convert ‘datetime2’ to ‘time’
Convert ‘datetimeoffset’ to …
- Convert ‘datetimeoffset’ to ‘datetime’
- Convert ‘datetimeoffset’ to ‘datetime2’
- Convert ‘datetimeoffset’ to ‘smalldatetime’
- Convert ‘datetimeoffset’ to ‘date’
- Convert ‘datetimeoffset’ to ‘time’
Convert ‘smalldatetime’ to …
- Convert ‘smalldatetime’ to ‘datetime’
- Convert ‘smalldatetime’ to ‘datetime2’
- Convert ‘smalldatetime’ to ‘datetimeoffset’
- Convert ‘smalldatetime’ to ‘date’
- Convert ‘smalldatetime’ to ‘time’
Convert ‘date’ to …
- Convert ‘date’ to ‘datetime’
- Convert ‘date’ to ‘datetime2’
- Convert ‘date’ to ‘smalldatetime’
- Convert ‘date’ to ‘datetimeoffset’