Tag: convert

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

Posted on by Ian

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 →

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

Posted on by Ian

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 →

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

Posted on by Ian

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

When you convert a time value to datetime2, extra information is added to the value. This is because the datetime2 data type contains both date and time information. The time data type, on the other hand, only contains time information.

More specifically, the date is set to ‘1900-01-01’ (unless it happens to get rounded up to ‘1900-01-02’), the time component is copied, and according to the Microsoft documentation, the time zone offset is set to 00:00 (even though the datetime2 data type is not time zone aware and doesn’t preserve any time zone offset).

When the fractional seconds precision of the datetime2(n) value is greater than the time(n) value, the value is rounded up.

Continue reading →

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

Posted on by Ian

This article contains examples of converting a time value to a datetimeoffset value in SQL Server using Transact-SQL.

When you convert a time value to datetimeoffset, the date is set to ‘1900-01-01’ and the time is copied. A time zone offset is added and set to +00:00.

Continue reading →

Examples of Converting ‘time’ to ‘datetime’ in SQL Server (T-SQL)

Posted on by Ian

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

When you convert a time value to datetime, extra information is added to the value. This is because the datetime data type contains both date and time information. The time data type, on the other hand, only contains time information. Therefore, date information is added to the value when you perform such a conversion. Specifically, the date is set to ‘1900-01-01’.

Continue reading →

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

Posted on by Ian

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

When you convert a date value to datetimeoffset, extra information is added to the value. This is because the datetimeoffset data type contains both date and time information, as well as the time offset information. In other words, the datetimeoffset data type defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. The date data type, on the other hand, only contains date information.

When we convert from date to datetimeoffset, the time (and time zone offset) is automatically added to the value. However, you can always change the value if required (including the time zone offset).

The datetimeoffset data type also allows you to specify the fractional seconds precision. If you don’t specify this, it uses a scale of 7. This means it will include 7 digits on the right side of the decimal point.

Continue reading →

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

Posted on by Ian

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

When you convert a date value to smalldatetime, extra information is added to the value. This is because the smalldatetime data type contains both date and time information. The date data type, on the other hand, only contains date information.

However, there are cases where a date to smalldatetime conversion might fail. In particular, if the date value is outside the range supported by smalldatetime then it will fail with an error.

In any case, below are examples of converting between these two data types.

Continue reading →

Examples of Converting ‘date’ to ‘datetime2’ in SQL Server (T-SQL)

Posted on by Ian

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

When you convert a date value to datetime2, extra information is added to the value. This is because the datetime2 data type contains both date and time information. The date data type, on the other hand, only contains date information.

The datetime2 data type is basically an extension of the datetime data type. It has a larger date range, a larger default fractional precision, and optional user-specified precision.

In any case, the conversion process is exactly the same regardless of the data type. The only difference is the amount of information that’s available between date, datetime and datetime2.

Continue reading →

Examples of Converting ‘date’ to ‘datetime’ in SQL Server (T-SQL)

Posted on by Ian

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

When you convert a date value to datetime, extra information is added to the value. This is because the datetime data type contains both date and time information. The date data type, on the other hand, only contains date information.

Continue reading →