Tag: mssql

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 →

How to Display a Date in German Format in SQL Server (T-SQL)

Posted on by Ian

When formatting a date using the FORMAT() function in SQL Server, the date will be formatted according to the language of your local session. However, you can override this by specifying a culture to use, or using a custom date format.

This article demonstrates how to explicitly specify a German date format by using the optional “culture” argument of the FORMAT() function. It also demonstrates how to use your own custom date format if that is more desirable.

Continue reading →

How to Display a Date in British Format in SQL Server (T-SQL)

Posted on by Ian

This article demonstrates how to explicitly format a date in Great Britain English format when using the T-SQL FORMAT() function in SQL Server.

You may or may not need to use this argument, depending on the language of your local session. However, here’s how to explicitly specify Great Britain English date format.

Continue reading →

How to Display a Date in US Date Format in SQL Server (T-SQL)

Posted on by Ian

In SQL Server, you can use the T-SQL FORMAT() function to display a date in the desired format. This function accepts an optional “culture” argument, which you can use to specify US date format.

You may or may not need to use this argument, depending on the language of your local session. However, here’s how to explicitly specify US date format.

Continue reading →

Remember This When Formatting a TIME Data Type in SQL Server (T-SQL)

Posted on by Ian

In SQL Server, when you use the T-SQL FORMAT() function to format a time data type, you need to remember to escape any colons or periods in your format string.

This is because the FORMAT() function relies upon CLR formatting rules, which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type.

Continue reading →