Tag: functions

Understanding ‘datetimeoffset’ Storage Size in SQL Server

Posted on by Ian

In this article I look at how the datetimeoffset data type is stored in SQL Server, and how you can get different reported storage size results, depending on what you’re doing with it.

This is similar to what I did with the datetime2 data type.

In particular, I look at the following:

  • Microsoft’s documentation
  • Data stored in a variable
    • Length in bytes using DATALENGTH()
    • Length in bytes using DATALENGTH() after converting to varbinary
  • Data stored in a database
    • Length in bytes using COL_LENGTH()
    • Length in bytes using DBCC PAGE()

Continue reading →

Understanding ‘datetime2’ Storage Size in SQL Server

Posted on by Ian

In this article I share some observations I’ve had regarding the datetime2 data type’s storage size in SQL Server. Perhaps I will clarify some points about the actual storage size used by this data type when stored in a database.

In particular, I look at the following:

  • Microsoft’s documentation
  • Data stored in a variable
    • Length in bytes using DATALENGTH()
    • Length in bytes using DATALENGTH() after converting to varbinary
  • Data stored in a database
    • Length in bytes using COL_LENGTH()
    • Length in bytes using DBCC PAGE()

Some of those seem to contradict each other, and you will see two different storage size amounts for the same value, depending on where you look.

Continue reading →

Convert ‘datetimeoffset’ to ‘time’ in SQL Server (T-SQL Examples)

Posted on by Ian

If you have a datetimeoffset value, but you don’t need the date and time zone offset part, converting it to time will save you a lot of storage space (while removing unnecessary details from the value). This article contains examples of converting a datetimeoffset value to a time value in SQL Server.

Continue reading →

Convert ‘datetimeoffset’ to ‘date’ in SQL Server (T-SQL Examples)

Posted on by Ian

If you have a datetimeoffset value, but you don’t need the time and time zone offset part, converting it to date will save you a lot of storage space (while removing unnecessary details from the value). This article contains examples of converting a datetimeoffset value to a date value in SQL Server.

Continue reading →

Convert ‘datetimeoffset’ to ‘smalldatetime’ in SQL Server (T-SQL Examples)

Posted on by Ian

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

When you convert a datetimeoffset value to smalldatetime, the date and hours are copied. The minutes are rounded up (depending on the seconds value) and the seconds are set to 0.

Continue reading →

Convert ‘datetimeoffset’ to ‘datetime2’ in SQL Server (T-SQL Examples)

Posted on by Ian

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.

Continue reading →

Convert ‘datetimeoffset’ to ‘datetime’ in SQL Server (T-SQL Examples)

Posted on by Ian

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.

Continue reading →

Convert ‘smalldatetime’ to ‘date’ in SQL Server (T-SQL Examples)

Posted on by Ian

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.

Continue reading →

Convert ‘smalldatetime’ to ‘datetime’ in SQL Server (T-SQL Examples)

Posted on by Ian

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.

Continue reading →