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.

Continue reading →

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.

Continue reading →

This article contains examples of converting a datetime2 value to a time value in SQL Server.

One of the benefits of converting a datetime2 value to time is that you reduce the storage size from between 6 and 8 bytes, down to between 3 and 5 bytes (depending on the precision each data type has assigned to it). Strictly speaking, 1 extra byte is used to store the precision for these data types, so you should add 1 byte to these amounts.

Obviously, you do lose the date portion during the conversion, but you wouldn’t be doing this conversion if you needed to retain the date portion.

Continue reading →

This article contains examples of converting a datetime2 value to a date value in SQL Server.

When you convert a datetime2 value to a date data type, you lose the time portion. However, you also reduce the storage size from between 7 and 9 bytes down to 3 bytes. In any case, you would only do this conversion if you don’t need the time portion.

Continue reading →

This article contains examples of converting a datetime value to a date value in SQL Server.

The obvious consequence of converting a datetime value to date is that you lose the time portion. However, one benefit is that you reduce the storage size from 8 bytes down to 3 bytes. Either way, you would only do this conversion if you don’t need the time portion.

Continue reading →

This article contains examples of converting a datetime value to a smalldatetime value in SQL Server.

One of the benefits of converting a datetime value to smalldatetime is that you reduce the storage size from 8 bytes down to 4 bytes. However, you do lose precision for doing so.

Continue reading →

This article contains examples of converting a datetime value to a time value in SQL Server.

One of the benefits of converting a datetime value to time is that you reduce the storage size from 8 bytes, down to either 3, 4, or 5 bytes (depending on the precision you use for the time value). Strictly speaking, time uses 4, 5, or 6 bytes, because an extra byte is used to store its precision.

Continue reading →

This article contains examples of converting a datetime value to a datetimeoffset value in SQL Server.

When you convert a datetime value to datetimeoffset, the resulting value will depend on the fractional seconds precision that you assign to datetimeoffset, as well as any time zone offset you specify.

The datetime data type has a maximum of 3 digits for its fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds.

The datetimeoffset 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). It also has a time zone offset and can preserve any offsets in the original value. However, datetime has no time zone awareness, so there are no existing values to preserve. In this case, the time zone offset defaults to +00:00.

SQL Server actually has the TODATETIMEOFFSET() function, which is specifically designed to convert a date/time value to datetimeoffset and add a time zone offset. However, there’s a subtle detail to be aware of when using this function, and I explain this below (with examples).

Continue reading →

This article contains examples of converting a datetime value to a datetime2 value in SQL Server.

When you convert a datetime value to datetime2, the resulting value will depend on the fractional seconds precision that you assign to datetime2.

The datetime data type has a maximum of 3 digits for its fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds.

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).

Continue reading →