Sql how to convert datetime to datetime

Database.Guide

Beginners

Categories

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

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.

When you convert a datetime2 value to datetime, the resulting value will depend on the fractional seconds that were assigned to the datetime2 value, as well as its precision.

The datetime2 data type allows you to specify a fractional seconds precision from 0 to 7. If you don’t specify this, it will use 7 (the default).

Therefore, if the datetime2 uses a scale of 3, the resulting value will be very close (if not identical) to the original value. However, due to the lower accuracy of datetime, the results can be different, because of the rounding that it performs.

Example 1 – Implicit Conversion

Here’s an example of an implicit conversion between datetime2 and datetime.

This is an implicit conversion because we’re not using a conversion function (like the ones below) to explicitly convert it. In this case, SQL Server performs an implicit conversion behind the scenes when we try to assign the datetime2 value to a datetime variable.

In this case, no rounding was performed.

Example 2 – Precision/Accuracy and Rounding

This also applies when converting from another data type (such as what we’re doing here).

Here’s an example that demonstrates what I mean.

It’s important to note that this will still be true, even if we assign only 3 fractional seconds to the datetime2 value.

It also pays to be aware of any rounding that might occur to the original datetime2 value. The datetime2 value could itself be rounded up if we try to assign a value with more fractional seconds than its own scale.

Both datetime2(3) and datetime use the same amount of storage space (8 bytes). The datetime2(3) data type actually uses 7 bytes to store the data, but extra 1 byte to store the precision.

Example 3 – Explicit Conversion using CAST()

Here’s an example of an explicit conversion. In this case, I use the CAST() function directly within the SELECT statement to explicitly convert between datetime2 and datetime.

Example 4 – Explicit Conversion using CONVERT()

How to convert datetime to date only (with time set to 00:00:00.000)

I have a string ‘2009-06-24 09:52:43.000’, which I need to insert to a DateTime column of a table.

But I don’t care about the time, just want to insert it as 2009-06-24 00:00:00.000

How can I do that in T-SQL?

11 Answers 11

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

For SQL Server 2005 and below:

For SQL Server 2008 and above:

I have found that casting as a date, then to a datetime to be very efficient and intuitive.

An enhancement to the unsupported version: I am not sure if this may effect any performance. getdate() is an input timestamp in my query.

select cast(cast(getdate() as DATE) as DATETIME)

James is correct. If you’re starting off with a string, and the format will always be what you say it is, then you keep it simple and efficient. Use LEFT( @StrDate, 10) and CONVERT that to your datetime value. Done.

If your input string could be any valid date/time format, then you have to use CONVERT(datetime, @StrDate) first. After that you go with what Bing just said to strip off the time part.

If you will always have the date in the same format, i.e. yyyy-MM-DD you can grab the first 10 characters if the value and insert that which is the equivelant of 00:00:00.0000 time for that date.

This is a very efficient way to do this as it does’t require converting data types HOWEVER, it does require that the date will always be formatted with a four digit year and two digit day & month.

cast it to a date, and then you can use CONVERT to get just the date.

CAST and CONVERT (Transact-SQL)

These functions convert an expression of one data type to another.

Syntax

Transact-SQL Syntax Conventions

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

data_type
The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.

length
An optional integer that specifies the length of the target data type, for data types that allow a user specified length. The default value is 30.

style
An integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.

Return types

Returns expression, translated to data_type.

Date and Time styles

For a date or time data type expression, style can have one of the values shown in the following table. Other values are processed as 0. Beginning with SQL Server 2012 (11.x), the only styles supported, when converting from date and time types to datetimeoffset, are 0 or 1. All other conversion styles return error 9809.

SQL Server supports the date format, in Arabic style, with the Kuwaiti algorithm.

Note: For a milliseconds (mmm) value of 0, the millisecond decimal fraction value will not display. For example, the value ‘2012-11-07T18:26:20.000 displays as ‘2012-11-07T18:26:20’.—127( 6, 7 )ISO8601 with time zone Z.yyyy-MM-ddThh:mm:ss.fffZ (no spaces)

Note: For a milliseconds (mmm) value of 0, the millisecond decimal value will not display. For example, the value ‘2012-11-07T18:26:20.000 will display as ‘2012-11-07T18:26:20’.—130 ( 1, 2 )Hijri ( 5 )dd mon yyyy hh:mi:ss:mmmAM

In this style, mon represents a multi-token Hijri unicode representation of the full month name. This value does not render correctly on a default US installation of SSMS.—131 ( 2 )Hijri ( 5 )dd/mm/yyyy hh:mi:ss:mmmAM

1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

2 The default values (0 or 100, 9 or 109, 13 or 113, 20 or 120, 23, and 21 or 25 or 121) always return the century (yyyy).

3 Input when you convert to datetime; output when you convert to character data.

4 Designed for XML use. For conversion from datetime or smalldatetime to character data, see the previous table for the output format.

5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.

By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That means that SQL Server interprets the two-digit year 49 as 2049 and the two-digit year 50 as 1950. Many client applications, including those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option to change the cutoff year used by SQL Server. This allows for the consistent treatment of dates. We recommend specifying four-digit years.

6 Only supported when casting from character data to datetime or smalldatetime. When casting character data representing only date or only time components to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

When converting smalldatetime to character data, the styles that include seconds or milliseconds show zeros in these positions. When converting from datetime or smalldatetime values, use an appropriate char or varchar data type length to truncate unwanted date parts.

When converting character data to datetimeoffset, using a style that includes a time, a time zone offset is appended to the result.

float and real styles

For a float or real expression, style can have one of the values shown in the following table. Other values are processed as 0.

money and smallmoney styles

For a money or smallmoney expression, style can have one of the values shown in the following table. Other values are processed as 0.

xml styles

For an xml expression, style can have one of the values shown in the following table. Other values are processed as 0.

Binary styles

For a binary(n), char(n), varbinary(n), or varchar(n) expression, style can have one of the values shown in the following table. Style values not listed in the table will return an error.

Implicit conversions

Implicit conversions do not require specification of either the CAST function or the CONVERT function. Explicit conversions require specification of the CAST function or the CONVERT function. The following illustration shows all explicit and implicit data type conversions allowed for SQL Server system-supplied data types. These include bigint, and sql_variant, and xml. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

The Microsoft Download Center has this chart available for download as a PNG file.

The above chart illustrates all the explicit and implicit conversions that are allowed in SQL Server, but the resulting data type of the conversion depends on the operation being performed:

A practical example on the effects of data type precedence in conversions can be seen later in this section.

Large-value data types

For more information about conversion from the xml data type, see Create Instances of XML Data.

xml data type

When you explicitly or implicitly cast the xml data type to a string or binary data type, the content of the xml data type is serialized based on a defined set of rules. For information about these rules, see Define the Serialization of XML Data. For information about conversion from other data types to the xml data type, see Create Instances of XML Data.

text and image data types

The text and image data types do not support automatic data type conversion. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000 bytes. If you try an incorrect conversion, for example trying to convert a character expression that includes letters to an int, SQL Server returns an error message.

Output Collation

When the CAST or CONVERT functions output a character string, and they receive a character string input, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default. For more information, see Collation Precedence (Transact-SQL).

To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:

SELECT CAST(‘abc’ AS varchar(5)) COLLATE French_CS_AS

Truncating and rounding results

When converting character or binary expressions (binary, char, nchar, nvarchar, varbinary, or varchar) to an expression of a different data type, the conversion operation could truncate the output data, only partially display the output data, or return an error. These cases will occur if the result is too short to display. Conversions to binary, char, nchar, nvarchar, varbinary, or varchar are truncated, except for the conversions shown in the following table.

* = Result length too short to display

E = Error returned because result length is too short to display.

SQL Server guarantees that only roundtrip conversions, in other words conversions that convert a data type from its original data type and back again, yield the same values from version to version. The following example shows such a roundtrip conversion:

Do not construct binary values, and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion, to binary, will be the same between versions of SQL Server.

The following example shows a resulting expression that is too small to display.

Here is the result set.

When you convert data types that differ in decimal places, SQL Server will sometimes return a truncated result value, and at other times it will return a rounded value. This table shows the behavior.

Results of the query are shown in the following table:

SELECT CAST(10.3496847 AS money);

SQL Server returns an error message when converting nonnumeric char, nchar, nvarchar, or varchar data to decimal, float, int, numeric. SQL Server also returns an error when an empty string (» «) is converted to numeric or decimal.

Certain datetime conversions are nondeterministic

The styles for which the string-to-datetime conversion is nondeterministic are as follows:

1 With the exception of styles 20 and 21

Supplementary characters (surrogate pairs)

Compatibility support

In earlier versions of SQL Server, the default style for CAST and CONVERT operations on time and datetime2 data types is 121, except when either type is used in a computed column expression. For computed columns, the default style is 0. This behavior impacts computed columns when they are created, used in queries involving auto-parameterization, or used in constraint definitions.

Under compatibility level 110 and higher, the CAST and CONVERT operations on the time and datetime2 data types always have 121 as the default style. If a query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query.

1 Except for computed columns

Upgrading the database to compatibility level 110 and higher will not change user data that has been stored to disk. You must manually correct this data as appropriate. For example, if you used SELECT INTO to create a table from a source containing a computed column expression described above, the data (using style 0) would be stored rather than the computed column definition itself. You must manually update this data to match style 121.

Examples

A. Using both CAST and CONVERT

Here is the result set. The sample result set is the same for both CAST and CONVERT.

B. Using CAST with arithmetic operators

This example calculates a single column computation ( Computed ) by dividing the total year-to-date sales ( SalesYTD ) by the commission percentage ( CommissionPCT ). This value is rounded to the nearest whole number and is then CAST to an int data type.

Here is the result set.

C. Using CAST to concatenate

This example concatenates noncharacter expressions by using CAST. It uses the AdventureWorksDW database.

Here is the result set.

D. Using CAST to produce more readable text

This example uses CAST in the SELECT list, to convert the Name column to a char(10) column. It uses the AdventureWorksDW database.

Here is the result set.

E. Using CAST with the LIKE clause

Here is the result set.

F. Using CONVERT or CAST with typed XML

These examples show use of CONVERT to convert data to typed XML, by using the XML Data Type and Columns (SQL Server).

This example converts a string with white space, text and markup into typed XML, and removes all insignificant white space (boundary white space between nodes):

This example converts a similar string with white space, text and markup into typed XML and preserves insignificant white space (boundary white space between nodes):

This example casts a string with white space, text, and markup into typed XML:

G. Using CAST and CONVERT with datetime data

Starting with GETDATE() values, this example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT to display the date and time in the ISO 8601 format.

Here is the result set.

This example is approximately the opposite of the previous example. This example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type.

Here is the result set.

H. Using CONVERT with binary and character data

These examples show the results of binary and character data conversion, using different styles.

Here is the result set.

This example shows that Style 1 can force result truncation. The characters 0x in the result set force the truncation.

Here is the result set.

This example shows that Style 2 does not truncate the result, because the result does not include the characters 0x.

Here is the result set.

Convert the character value ‘Name’ to a binary value.

Here is the result set.

Here is the result set.

Here is the result set.

I. Converting date and time data types

This example shows the conversion of date, time, and datetime data types.

J. Using CONVERT with datetime data in different formats

Starting with GETDATE() values, this example uses CONVERT to display of all the date and time styles in section Date and Time styles of this article.

K. Effects of data type precedence in allowed conversions

The following example defines a variable of type VARCHAR, assigns an integer value to the variable, then selects a concatenation of the variable with a string.

Here is the result set.

The int value of 1 was converted to a VARCHAR.

This example shows a similar query, using an int variable instead:

In this case, the SELECT statement will throw the following error:

If we provide a string that can be converted, the statement will succeed, as seen in the following example:

In this case, the string ‘1’ can be converted to the integer value 1, so this SELECT statement will return the value 2. When the data types provided are integers, the + operator becomes addition mathematical operator, rather than a string concatenation.

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

L. Using CAST and CONVERT

This example retrieves the name of the product for those products that have a 3 in the first digit of their list price, and converts the ListPrice of these products to int. It uses the AdventureWorksDW2016 database.

This example shows the same query, using CONVERT instead of CAST. It uses the AdventureWorksDW2016 database.

M. Using CAST with arithmetic operators

This example calculates a single column value by dividing the product unit price ( UnitPrice ) by the discount percentage ( UnitPriceDiscountPct ). This result is then rounded to the nearest whole number, and finally converted to an int data type. This example uses the AdventureWorksDW2016 database.

Here is the result set.

N. Using CAST with the LIKE clause

This example converts the money column ListPrice to an int type, and then to a char(20) type, so that the LIKE clause can use it. This example uses the AdventureWorksDW2016 database.

O. Using CAST and CONVERT with datetime data

This example displays the current date and time, uses CAST to change the current date and time to a character data type, and finally uses CONVERT display the date and time in the ISO 8601 format. This example uses the AdventureWorksDW2016 database.

Here is the result set.

This example is the rough opposite of the previous example. This example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type. This example uses the AdventureWorksDW2016 database.

Функции CAST и CONVERT (Transact-SQL)

Эти функции преобразуют выражение одного типа данных в другой.

Синтаксис

Синтаксические обозначения в Transact-SQL

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

data_type
Целевой тип данных. Это может быть xml, bigint и sql_variant. Псевдонимы типов данных недопустимы.

length
Указываемое дополнительно целое число, обозначающее длину целевого типа данных, который допускает указание длины пользователем. Значение по умолчанию — 30.

style
Целочисленное выражение, определяющее, как функция CONVERT преобразует значение аргумента expression. Для значения стиля NULL возвращается NULL. Аргумент data_type определяет диапазон.

Типы возвращаемых данных

Возвращает значение аргумента expression, преобразованное в тип data_type.

Стили даты и времени

Если аргумент expression принадлежит к типу данных даты или времени, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0. Начиная с SQL Server 2012 (11.x), преобразование типов даты и времени в datetimeoffset поддерживается только для стилей 0 и 1. Все другие стили преобразования возвращают ошибку 9809.

SQL Server поддерживает формат даты в арабском стиле, используя кувейтский алгоритм.

Примечание. Для равного 0 значения миллисекунд (ммм) значение десятичной части не отображается. Например, значение «2012-11-07T18:26:20.000» отображается как «2012-11-07T18:26:20».—127( 6, 7 )ISO8601 с часовым поясом П.гггг-ММ-ддTчч:мм:сс.fffZ (без пробелов)

Примечание. Для равного 0 значения миллисекунд (ммм) значение десятичной части не отображается. Например, значение «2012-11-07T18:26:20.000» будет отображаться как «2012-11-07T18:26:20».—130 ( 1, 2 )Хиджра ( 5 )дд мес гггг чч:ми:сс:мммAM

В этом стиле мес является представлением Юникода полного названия месяца в Хиджре. Это значение не отображается правильно при установке SSMS языковой версии «Английский (США)» по умолчанию.—131 ( 2 )Хиджра ( 5 )дд/мм/гггг чч:ми:сс:мммAM

1 Эти значения стилей возвращают недетерминированные результаты. Включают в себя все стили «гг» (без номера века) и часть стилей «гггг» (с номером века).

2 Значения по умолчанию (0 или 100, 9 или 109, 13 или 113, 20 или 120, 23 и 21 или 25 или 121) всегда возвращают год с веком (гггг).

3 Вход при преобразовании в тип datetime; выход при преобразовании в символьные данные.

4 Для использования в формате XML. Для преобразования из datetime или smalldatetime в символьные данные формат вывода должен быть таким, как описано в предыдущей таблице.

5 Хиджра — календарная система с несколькими вариантами. В SQL Server используется кувейтский алгоритм.

По умолчанию SQL Server интерпретирует двузначные значения года с пороговым значением 2049. Это означает, что SQL Server интерпретирует двухзначное значение года 49 как 2049, а двухзначное значение 50 — как 1950. В большинстве клиентских приложений, основанных, в частности, на объектах автоматизации, 2030 год используется в качестве порогового значения. SQL Server предоставляет параметр настройки двузначного порогового значения года, с помощью которого можно изменить пороговое значение года, используемое в SQL Server. Это позволяет обеспечить согласованную обработку дат. Рекомендуется использовать четырехзначные года.

6 Поддерживается только при приведении символьных данных к типу datetime или smalldatetime. При приведении символьных данных, представляющих только дату или только время, к типам datetime и smalldatetime неуказанное время устанавливается в 00:00:00.000, а неуказанная дата — в 1900-01-01.

При преобразовании в символьные данные из smalldatetime стили, включающие секунды или миллисекунды, будут содержать нули в соответствующих позициях. При преобразовании из datetime или smalldatetime ненужные части даты можно усекать с помощью типа данных char или varchar соответствующей длины.

При преобразовании в тип данных datetimeoffset из символьных данных со стилем, включающим время, смещение часового пояса добавляется к результату.

Стили данных float и real

Если аргумент expression принадлежит к типу данных float или real, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0.

Стили данных money и smallmoney

Если аргумент expression принадлежит к типу данных money или smallmoney, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0.

Стили данных XML

Если аргумент expression принадлежит к типу данных xml, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0.

Стили двоичных данных

Если аргумент expression принадлежит к типу данных binary(n), char(n), varbinary(n) или varchar(n), аргумент style может иметь одно из значений, приведенных в таблице ниже. При использовании значений стиля, отсутствующих в этой таблице, возвращается ошибка.

Неявные преобразования

Для неявных преобразований не требуется указывать функции CAST или CONVERT. Для явных преобразований указывать функции CAST или CONVERT необходимо. На следующей иллюстрации показаны все явные и неявные преобразования типов данных, допустимые для системных типов данных SQL Server. Это могут быть типы bigint, sql_variant и xml. При присваивании неявного преобразования из типа sql_variant не происходит, но неявное преобразование в тип sql_variant производится.

В Центре загрузки Майкрософт эта диаграмма представлена в виде PNG-файла, который можно скачать.

На приведенной выше диаграмме показаны все явные и неявные преобразования, которые допускаются в SQL Server, однако результирующий тип данных преобразования зависит от выполняемой операции.

Практический пример применения приоритета типов данных при преобразованиях приводится далее в этом разделе.

Типы данных больших значений

Типы данных большого объема демонстрируют то же поведение при явных и неявных преобразованиях, что и их аналоги меньшего объема, а именно типы данных nvarchar, varbinary и varchar. Тем не менее необходимо учитывать следующие правила:

Дополнительные сведения о преобразовании из типа данных xml см. в разделе Создание экземпляров XML-данных.

Тип данных XML

При явном или неявном приведении типа данных xml к строковому или двоичному типу данных содержимое типа данных xml сериализуется согласно набору определенных правил. Сведения об этих правилах см. в разделе Определение сериализации XML-данных. Дополнительные сведения о преобразовании других типов данных в тип данных xml см. в разделе Создание экземпляров XML-данных.

Типы данных text и image

Для типов данных text и image автоматическое преобразование типов не поддерживается. Можно явно преобразовать text в символьные данные, а image — в binary или varbinary, но длиной не более 8000 байт. Если вы попробуете произвести неверное преобразование, например преобразовать символьное выражение, содержащее буквы, в int, SQL Server вернет ошибку.

Параметры сортировки выходных данных

Если входные и выходные данные функций CAST и CONVERT представляют собой символьные строки, у выходных данных будут те же параметры сортировки, что и у входных. Если входные данные не символьная строка, выходным назначаются параметры сортировки по умолчанию для этой базы данных и присваивается метка о том, что этот режим используется принудительно. Дополнительные сведения см. в разделе Очередность параметров сортировки (Transact-SQL).

Чтобы назначить выходным данным другие параметры сортировки, примените предложение COLLATE к результирующему выражению функции CAST или CONVERT. Пример:

SELECT CAST(‘abc’ AS varchar(5)) COLLATE French_CS_AS

Усечение и округление результатов

При преобразовании символьных или двоичных выражений (binary, char, nchar, nvarchar, varbinary или varchar) в выражение другого типа данных операция преобразования может усекать выходные данные, отображать их лишь частично или возвращать ошибку. Это происходит в тех случаях, когда результат имеет слишком малую длину для отображения. Результаты преобразований в binary, char, nchar, nvarchar, varbinary или varchar усекаются всегда, за исключением случаев, перечисленных в таблице ниже.

* = результат слишком мал для отображения

О = ошибка, так как длина результата слишком мала для отображения.

SQL Server гарантирует получение одинаковых результатов в разных версиях только для обратимых преобразований, то есть таких, когда данные преобразуются из исходного типа данных, а затем опять в него. В следующем примере показано обратимое преобразование:

Не пытайтесь составлять данные типа binary, а затем преобразовывать их в данные категории числового типа. SQL Server не гарантирует, что результат преобразования типа данных decimal или numeric в binary будет одинаковым в разных версиях SQL Server.

В следующем примере показано результирующее выражение, которое слишком мало для отображения:

При преобразовании между типами данных с разными длинами дробных частей SQL Server может усекать или округлять результат. В следующей таблице описано это поведение.

Например, значения 10,6496 и –10,6496 могут усекаться или округляться при преобразовании в тип int или numeric:

Результаты запроса показаны в приведенной ниже таблице.

SELECT CAST(10.3496847 AS money);

SQL Server возвращает сообщение об ошибке при попытке преобразовать нечисловые данные типа char, nchar, nvarchar или varchar в тип decimal, float, int или numeric. SQL Server также возвращает сообщение об ошибке при попытке преобразования пустой строки (» «) в тип numeric или decimal.

Некоторые преобразования типа данных даты и времени являются недетерминированными

В следующих стилях преобразование строк в тип datetime недетерминировано:

1 За исключением стилей 20 и 21.

Дополнительные символы (суррогатные пары)

Поддержка совместимости

В предыдущих версиях SQL Server используемый по умолчанию стиль для операций CAST и CONVERT с типами данных time и datetime2 — 121, кроме случая, когда любой из типов используется в выражении вычисляемого столбца. Для вычисляемых столбцов используемый по умолчанию стиль — 0. Это поведение влияет на вычисляемые столбцы при их создании и использовании в запросах с автоматической параметризацией, а также при использовании в определениях ограничений.

При уровне совместимости 110 и выше стиль по умолчанию для операций CAST и CONVERT с типами данных time и datetime2 всегда имеет значение 121. Если запрос основан на прежнем поведении, следует использовать уровень совместимости ниже 110 либо явно задать в затрагиваемом запросе стиль 0.

1 Кроме вычисляемых столбцов

Обновление базы данных до уровня совместимости 110 и выше не приведет к изменению пользовательских данных, сохраненных на диске. Следует исправить эти данных соответствующим образом вручную. Например, если бы вы использовали предложение SELECT INTO для создания таблицы на основе источника, содержащего описанное выше выражение вычисляемого столбца, то сохранялись бы данные (благодаря стилю 0), а не само определение вычисляемого столбца. В таком случае необходимо вручную обновлять эти данные в соответствии со стилем 121.

Примеры

A. Использование функций CAST и CONVERT

Результирующий набор: Образец результирующего набора одинаков для операций CAST и CONVERT.

Б. Использование функции CAST с арифметическими операторами

В. Использование функции CAST для объединения строк

В этом примере несимвольные выражения сцепляются с помощью функции CAST. В этом примере используется база данных AdventureWorksDW.

Г. Использование функции CAST для получения удобочитаемого текста

Д. Использование функции CAST с предложением LIKE

Е. Использование функции CONVERT или CAST с типизированным XML

В этих примерах показано использование функции CONVERT для преобразования данных в типизированный XML-код с помощью столбцов и типа данных XML (SQL Server).

В этом примере строка, содержащая пробельные символы, текст и разметку, преобразуется в типизированный XML, в котором удаляются все незначащие пробельные символы (пробелы, разделяющие узлы):

В этом примере похожая строка, содержащая пробельные символы, текст и разметку, преобразуется в типизированный XML, в котором сохраняются все незначащие пробельные символы (пробелы, разделяющие узлы):

В этом примере строка, содержащая пробельные символы, текст и разметку, приводится к типизированному XML:

Дополнительные примеры см. в статье Создание экземпляров XML-данных.

Ж. Использование функций CAST и CONVERT с данными типа datetime

З. Использование функции CONVERT с двоичными и символьными данными

В этих примерах показаны результаты преобразования двоичных и символьных данных с использованием различных стилей.

В этом примере показано, что стиль 1 может привести к принудительному усечению результата. К этому приводит наличие символов 0x в результате.

В этом примере показано, что стиль 2 не вызывает усечение результата, так как символы 0x не включены в результат.

Преобразуйте символьное значение «Name» в двоичное значение.

И. Конвертирование типов данных даты и времени

Этот пример демонстрирует преобразование типов данных date, time и datetime.

К. Использование функции CONVERT с данными типа datetime в разных форматах

Начиная со значений GETDATE() этот пример использует функцию CONVERT для отображения всех стилей даты и времени в разделе Стили даты и времени этой статьи.

Л. Результат применения приоритета типов данных при допустимых преобразованиях

В приведенном ниже примере определяется переменная типа VARCHAR, ей присваивается значение, а затем выбирается объединение переменной со строкой.

Значение 1 типа int было преобразовано в тип VARCHAR.

В этом примере показан похожий запрос с переменной типа int:

В этом случае инструкция SELECT выдает следующую ошибку:

Если предоставить строку, которую можно преобразовать, инструкция завершается успешно, как показано в следующем примере:

В этом случае строка ‘1’ может быть преобразована в целочисленное значение 1, поэтому инструкция SELECT возвращает значение 2. Если предоставленные типы данных являются целочисленными, оператор + становится математическим оператором сложения, а не объединения строк.

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

М. Использование функций CAST и CONVERT

Н. Использование функции CAST с арифметическими операторами

О. Использование функции CAST с предложением LIKE

П. Использование функций CAST и CONVERT с данными типа datetime

SQL Server Convert Datetime to date + Examples

In this SQL Server tutorial, we will discuss How to convert a DateTime to Date in SQL Server, Different SQL Server Conversion Scenarios, and cover the below topic.

For all the examples, I have used sql server 2019 and sql server management studio.

SQL Server Convert Datetime to date mm/dd/yyyy

In SQL Server to convert a DateTime expression to a specific mm/dd/yyyy format, we can use the Convert() function. In Convert() function we can specify the format that we want as a result by using the style parameter.

The query will return the following result.

SQL Server Convert Datetime to date string

In above the queries, we have used the GETDATE() function to get the current system DateTime value, but we can specify any DateTime value instead of the GETDATE() function in the Convert() function.

SQL Server Convert Datetime to date without time

Now to convert a Datetime value to only a date expression without any time, there are 2 functions available in SQL Server that we can use.

Using Cast() to convert Datatime to date without time

In the above query first, we are using the GETDATE() function to get the current system DateTime value. And after this we are using the Cast() function to convert the Datetime value to a date expression without any time. And it will return the following output.

Using Convert() to convert Datatime to date without time

In the above query, we are using the Convert() function to change the data type of a Datetime expression to varchar. And we are also defining style as 23 which will return the date in yyyy-mm-dd format. We will get the following result out of it.

SQL Server Convert Datetime to date and time

In SQL Server, we can easily convert a standard DateTime format to any other DateTime format using the Convert() function. So by using the Convert() function first we have to convert the DateTime format to varchar and then we can specify the required Datetime format.

And there are various Datetime format options available in the Convert() function which is listed below.

SQL Server Convert Datetime to date hour

So there are 2 functions available in SQL Server that we can use to convert a Datetime expression to a date hour expression (yyyy-MM-dd:HH). And we will discuss both the function below.

Using Format() to convert Datetime to date hour

The Format() function in SQL Server is used to change the format of numeric or date/time expressions. So we can use the Format() function to convert the Datetime expression to date hour by using the following query.

In the above query, we are using the GETDATE() function, which returns the current DateTime value. And then we are using the Format() function to change the Datetime format to ‘yyyy-MM-dd:HH’. After successful execution, it will return the following result.

Using Convert() to convert Datetime to date hour

Now we are going to use the Covert() function for converting Datetime value to date hour. But, the Convert() function cannot directly convert DateTime to date hour. For this, we have to add some string manipulation within the query.

In the above query, we are using the Convert() function to first get the date expression out of the Datetime expression and then getting the hours part from the Datetime. In the end, we are concatenating the result. And it will return the following result.

SQL Server Convert Datetime to date column

Now to convert a Datetime column to a simple date column, there are 2 functions available in SQL Server.

First is the Cast() function and second is the Convert() function. And both are conversion functions that are used to convert the expression of one data type to another data type. And we will discuss both with the help of an example.

For demostration consider the following sample table containing random datetime values.

Now we will try to convert the Datetime column to a date column using both Cast() and Convert() functions.

Using Cast() function

In the above query, we are fetching the DateTime column from our table and then we are using the CAST() function to convert the DateTime Column into the date. And it will return the following result.

Using Convert() function

In the above query, first, we are fetching the DateTime column from the table then we are using the Convert() function to change the Datetime data type to varchar. And we are defining the style parameter as 103 to get the proper date format. After successful execution, it will return the following result.

SQL Server Convert Datetime to datetimeoffset

There are many ways through which we can convert a Datetime expression to datetimeoffset. When converting a DateTime value to datetimeoffset, the result depends on the fractional seconds precision that we give to datetimeoffset, as well as any time zone offset we specify.

The fractional seconds component of the DateTime data type can have up to three digits. Its precision is rounded to the nearest 0.000, 0.003, or 0.007 seconds. On the other side, the datetimeoffset data type allows you to define a fractional seconds precision from 0 to 7.

The datetimeoffset has a time zone offset as well as the ability to keep any offsets from the original value. Datetime, on the other hand, does not have any time zone value.

Now let’s discuss 2 main functions that we can use to convert a Datetime expression to datetimeoffset.

Using Cast() function

In the above query, we get the Datetime value using the GETDATE() function, and then we are using the Cast() function to convert the Datetime value to datetimeoffset. In the end, it will return the following result.

Using Convert() function

In the above query, first, we get the Datetime value using the GETDATE() function, and then we are using the Convert() function to convert the Datetime data type to datetimeoffset data type. So after execution, it will return the following result.

SQL Server Convert Datetime to date in query

We can easily convert a Datetime expression to a simple date value by using multiple conversion functions within a query. Let’s understand the implementation with the help of an example.

For example, assume we have the following employee table.

The Joining date field in the above table contains Datetime values. So we are going to use the Convert() function within our query to convert the Datetime value to a date value. And then we are going to use the converted value to filter out results

In the above query first, we are using the SELECT statement to get all the columns, and then we are using the WHERE clause. In the WHERE clause, we are using the Convert() function to change the joining date to a specific dd-mm-yy date format. And then we are using a comparison operator to filter out results.

SQL Server Convert Datetime to datetime2

The datetime2 in SQL Server is another data type that is used to define date and time values together. We can consider datetime2 as an extension of DateTime which has a larger date range, a larger default fractional precision, and optional user-specified precision.

Converting datetime to datetime2 using Cast()

In the above example, we are using the Cast() conversion function within the SELECT query to explicitly convert the DateTime expression to the datetime2 expression. And we have also defined the fractional seconds precision for datetime2 as 7. This means that the fractional second’s value in resulting datetime2 will have up to 7.

In the end, the above query will return the following result.

Converting datetime to datetime2 using Convert()

In the above query, we are using the Convert() conversion function within the SELECT query to convert the DateTime expression to the datetime2 expression. And we have also defined the fractional seconds precision for datetime2 as 7. After execution, it will return the following output.

SQL Server Convert Datetime to DateTime without milliseconds

Now there are multiple ways through which we can remove the milliseconds part from a Datetime expression. We are discussing a few of them below.

In the above query, we are using the Cast() function to change the Datetime expression to datetime2. And for datetime2, we have defined the fractional seconds precision as 0. So, the result from it we will not have any fractional seconds.

So in the next query, we are using the Convert() function to convert the Datetime value to datetime2. And again for datetime2, we have defined the fractional seconds precision as 0. So, in the end, the result will not have any fractional seconds.

In the final query, we are using the Convert() function to change the Datetime datatype to varchar. And we have also specified the style parameter as 120. Using which the millisecond will be removed from the result.

All the queries defined in this section are used to remove the milliseconds from the Datetime in SQL Server. All will return the same result

SQL Server Convert Datetime to date in where clause

We can also convert the Datetime data type to a date data type within a WHERE clause in SQL Server. So we can directly convert the Datetime data type to date in the WHERE clause and use it for comparison. Let’s understand this with the help of an example.

Now for demonstration, let’s consider the following sample table having multiple DateTime records.

And now we will try to convert the Datetime column to date within the WHERE clause and make a comparison to extract some records.

So in the above query, we are using the SELECT statement to select the DateTime column. After this, we are putting a WHERE clause. And within the WHERE clause, first, we are using the Convert() function to change the Datetime value to a specific dd-mm-yy date format. In the end, we are making a comparison using the converted value.

Hence the query will return the following output.

SQL Server Convert Datetime to UTC

The UTC is our date and time value as it would be in Coordinated Universal Time and to get the current UTC DateTime in SQL Server we can use the GETUTCDATE() function. This function returns the current system DateTime as per Coordinated Universal Time.

So now, let’s understand how we can convert a Datetime value to a UTC DateTime. The SQL Server 2016 or later versions supports multiple UTC zones. And for conversion, we can use any supported UTC zone.

But we cannot directly convert a Datetime value to UTC Datetime. For conversion, we have to use Convert() function with AT TIME ZONE clause.

In the above query, first, we are using the GETDATE() function within the SELECT statement to get the current system Datetime value. After this, we are using the Covert() function to change the Datetime value to UTC Datetime value. And we’re using the AT TIME ZONE clause to define our UTC zone. So after execution, it will return 2 values first is the system Datetime value, and the second is the converted value.

SQL Server Convert Datetime to UTC String

The SQL Server 2016 or later versions supports multiple UTC zones. And we can define our UTC zone by using AT TIME ZONE clause. Now to convert a Datetime expression to a UTC string we can use the following query.

So in the query above, we are using the GETDATE() function to get the current DateTime value. After this, we are using the Convert() function to convert the Datetime data type to varchar. And within the Convert() function we have defined our UTC zone as “Central America Standard Time“. In the end, it will return the following result.

Convert UTC DateTime to PST Datetime in SQL Server

The pst DateTime is used to represent Pacific Standard Time. And as SQL Server 2016 or later supports multiple UTC zone. We can easily convert the local UTC Datetime to PST Datetie by using the query below.

So in the above query, we are using the GETUTCDATE() function in the SELECT statement to get UTC Datetime. After this, we are using the Convert() function to change UTC Datetime to PST Datetime. And for this, we also have to define the PST Datetime in AT TIME ZONE clause

After execution, it will return thr fllowing result.

You may like following SQL Server tutorials:

In this SQL Server tutorial, we have learned How to convert a DateTime to Date in SQL Server, Different SQL Server Conversion Scenarios, and cover the below topic.

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.