Вопрос по datetime, implicit-conversion, sql-server, implicit – Как SQL Server определяет формат для неявного преобразования даты и времени?

15
<code>declare @str_datetime varchar(50)
set @str_datetime='30-04-2012 19:01:45' -- 30th April 2012
declare @dt_datetime datetime
select @[email protected]_datetime
</code>

Это дает следующую ошибку:

Msg 242, Level 16, State 3, Line 4
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

У меня вопрос, как SQL Server решает, какой формат использовать для неявного преобразования даты и времени?

Ваш Ответ

3   ответа
25

ционной системы, текущих настроек языка и даты в формате текущего пользователя. По умолчанию Windows используетUS Englishи пользовательские настройкиUS English а такжеMDY.

Но вот несколько примеров, чтобы показать, как это может измениться.

Пользователь использует настройки британского языка:

-- works:
SET LANGUAGE BRITISH;
SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');

-- fails:
SELECT CONVERT(DATETIME, '04/13/2012');
GO

(Ошибка)

Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Пользователь использует Fran & # xE7; ais:

-- works:
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');

-- fails:
SELECT CONVERT(DATETIME, '04/13/2012');
GO

(Ошибка)

Msg 242, Level 16, State 3, Line 1
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Пользователь снова использует Fran & # xE7; ais:

SET LANGUAGE FRENCH;

-- fails (proving that, contrary to popular belief, YYYY-MM-DD is not always safe):
SELECT CONVERT(DATETIME, '2012-04-30');
GO

(Ошибка)

Msg 242, Level 16, State 3, Line 1
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Пользователь использует DMY вместо MDY:

SET LANGUAGE ENGLISH;
SET DATEFORMAT DMY;

-- works:
SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');

-- fails:
SELECT CONVERT(DATETIME, '04-30-2012');
GO

(Ошибка)

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Лучше всего всегда использовать стандартные, не региональные, безопасные и однозначные форматы дат ISO. Два, которые я обычно рекомендую:

YYYYMMDD                  - for date only.
YYYY-MM-DDTHH:MM:SS[.mmm] - for date + time, and yes that T is important.

Ничто из этого не терпит неудачу:

SET DATEFORMAT MDY;
SET LANGUAGE ENGLISH;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');
SET LANGUAGE BRITISH;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');
SET DATEFORMAT DMY;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');

Поэтому я настоятельно рекомендую вместо того, чтобы разрешать пользователям вводить произвольные текстовые форматы дат (или использовать ненадежные форматы самостоятельно), контролировать свои входные строки и следить за тем, чтобы они соответствовали одному из этих безопасных форматов. Тогда не будет иметь значения, какие настройки пользователь имеет или какие региональные настройки лежат в основе, ваши даты всегда будут интерпретироваться как даты, для которых они были предназначены. Если вы в настоящее время разрешаете пользователям вводить даты в текстовое поле формы, прекратите это делать и внедрите элемент управления календаря или хотя бы список выбора, чтобы в конечном итоге вы могли контролировать формат строки, который передается обратно на SQL Server.

Для получения дополнительной информации, пожалуйста, прочитайте Tibor Karaszi 's& quot; Полное руководство по типам данных datetime & quot; и мой пост& quot; Плохие привычки к удару: неправильная обработка запросов даты / диапазона. & quot;

@ Или я думаю, что вы упускаете из виду то, что я пытался сделать. Если один человек находится в Англии и передает строку «09.04.2014». а другой человек находится в США и проходит «09/04/2014», как ваша функция волшебным образом узнает, какой из них имел в виду 9 апреля, а какой - 4 сентября?
Я должен признать, что я не прочитал всю вашу ветку, я искал решение для перевода на месяц, и пока я искал его, я наткнулся на ваш ответ, поэтому я подумал, что некоторые люди могут найти его полезным ...
Смотри мой ответhere как это сделать в пользовательской функции.
1

MM/DD/YY, unless a localized version of SQL Server has been installed. This setting is fine for cases when an application that requires this functionality is deployed in a manner guaranteeing that dates are used and inserted in the same format across all platforms and locations where the application is used.

However, in some cases the date must be in a DD/MM/YY format because many countries/regions use this format rather than the U.S. default of MM/DD/YY. This is especially an issue for international applications that are distributed all over the world.

Источник

Итак, вам нужно установить формат даты перед этим, как показано ниже:

SET DATEFORMAT dmy
GO

И тогда ваш запрос будет работать.

Нет, это не так. Вам нужно контролировать строку ввода. Если пользователь проходит05/06/2012Поскольку им было разрешено набирать его в любом формате, который им нужен, как SQL Server может надежно определить, имел ли пользователь в виду 6 мая или 5 июня?
@AaronBertrand Правильно. Есть ли безошибочные подходы, чтобы справиться с этим на SQL? Я имею в виду, получить строку, представляющую дату в любом формате, и выполнить преобразование безопасно, без всякой неприятной логики?
... пока кто-то не использует другой формат.
1

систему и / или для каждого последующего добавления входа в систему (без необходимости выполнять «SET DATEFORMAT» в каждом сеансе.

Заходите в SQL Management Studio / Безопасность / Логины. Щелкните правой кнопкой мыши логин, затем Свойства. Вы увидите & quot; Язык по умолчанию & quot; внизу.

Если вы хотите убедиться, что все логины, добавленные в будущем, получат & quot; хорошо & quot; язык. Вы щелкаете правой кнопкой мыши на корне вашего сервера, затем на странице «Свойства» и «Дополнительно». Существует опция «Язык по умолчанию». там тоже, что используется для вновь созданных логинов.

Вы не должны просто комментировать "спасибо" вот .... но мне нужно, так что: спасибо.

Похожие вопросы