Почему SQL Server не позволяет мне хранить «21/04/17» в качестве даты?

У меня есть таблица, в которой в настоящее время все столбцы хранятся как nvarchar (max), поэтому я конвертирую все типы данных, чтобы они были такими, какими они должны быть. У меня есть столбец дат, однако, когда я запускаю это:

ALTER TABLE Leavers ALTER COLUMN [Actual_Termination_Date] date;

я получил

"Conversion failed when converting date and/or time from character string".

Это относительно нормально, поэтому я сделал следующее расследование:

SELECT DISTINCT TOP 20 [Actual_Termination_Date]
FROM LEAVERS
WHERE ISDATE([Actual_Termination_Date]) = 0

который вернулся:

NULL
13/04/2017
14/04/2017
17/04/2017
19/04/2017
21/04/2017
23/04/2017
24/04/2017
26/04/2017
28/04/2017
29/03/2017
29/04/2017
30/04/2017
31/03/2017
42795
42797
42813
42817
42820
42825

Форматы дат в стиле null и excel (например, 42795) не представляют проблемы, однако именно эти даты выглядят как совершенно нормальные даты, с которыми у меня проблемы. Я обычно исправляю подобные проблемы, используя одно из следующих исправлений:

SELECT  cast([Actual_Termination_Date] - 2 as datetime)
FROM LEAVERS
WHERE ISDATE([Actual_Termination_Date]) = 0

или же

SELECT  cast(convert(nvarchar,[Actual_Termination_Date], 103) - 2 as datetime)
FROM LEAVERS
WHERE ISDATE([Actual_Termination_Date]) = 0

Когда они возвращают даты назад, как я хотел бы, я бы сделал оператор UPDATE, чтобы изменить их в таблице, а затем преобразовал тип столбца. Однако я получаю сообщение об ошибке, в котором говорится, что различные даты не могут быть преобразованы, например:

Conversion failed when converting the nvarchar value ད/04/2017' to data type int.

есть идеи? Спасибо!

Всего 4 ответа


Вероятно, из-за вашего языка. Чтобы « '21/04/2017' работал, вам нужно использовать BRITISH язык или другой язык, который использует dd/MM/yyyy . Я подозреваю, что вы используете английский, который на самом деле американский.

Использование американцами MM/dd/yyyy означает, что « '21/04/2017' будет означать 4-й день 21-го месяца в 2017 году; Очевидно, это не работает.

Лучший способ - использовать однозначный формат независимо от языка и типа данных. Для SQL Server это yyyyMMdd и yyyy-MM-ddThh:mm:ss.nnnnnnn ( yyyy-MM-dd и yyyy-MM-dd hh:mm:ss.nnnnnnn не являются однозначными в SQL Server при использовании более smalldatetime данных datetime и smalldatetime типов).

В противном случае вы можете использовать CONVERT с кодом стиля :

SELECT CONVERT(date,ད/04/2017', 103)

Однако проблема с вашими данными заключается в том, что у вас есть значения в формате dd/MM/yyyy и целочисленные значения. Значение int (не varchar ) 42817 в качестве datetime в SQL Server равно 2017-03-25 . С другой стороны, если эти данные получены из Excel, то значение равно 2017-03-23 . Я собираюсь предположить, что данные поступили из Excel, а не с SQL Server (потому что драйверы ACE имеют привычку считывать даты в виде чисел, потому что они не «асы»).

Поэтому вам нужно сначала преобразовать значения в однозначный формат, так что это будет yyyyMMdd . Поскольку у нас есть 2 различных типа значений, это немного сложнее, но все же возможно:

UPDATE dbo.Leavers
SET Actual_Termination_Date = CONVERT(varchar(8), ISNULL(TRY_CONVERT(date, Actual_Termination_Date, 103), DATEADD(DAY, TRY_CONVERT(int, Actual_Termination_Date),�')), 112);

Тогда вы можете изменить свой стол:

ALTER TABLE dbo.Leavers ALTER COLUMN [Actual_Termination_Date] date;

DB <> Fiddle, используя оператор DML Михаила Турчина.


Сначала поместите столбец в канонический формат, затем преобразуйте:

update leavers
    set Actual_Termination_Date = try_convert(date, [Actual_Termination_Date], 103);

ALTER TABLE Leavers ALTER COLUMN [Actual_Termination_Date] date;

update будет выполнять неявное преобразование даты в строку. alter должен иметь возможность "отменить" это неявное преобразование.

Сделайте резервную копию таблицы, прежде чем сделать это! Скорее всего, вы обнаружите, что некоторые даты недопустимы - это правило, когда вы храните даты в виде строк, хотя в небольшом количестве случаев все строки дат фактически имеют одинаковый формат.


Фактическая дата не имеет значения. Ошибка происходит при попытке вычесть 2 из строки:

[Actual_Termination_Date] - 2

Подсказка приходит из сообщения об ошибке:

Ошибка преобразования при преобразовании значения nvarchar '21/04/2017' в тип данных int .

Чтобы исправить проблему, используйте DATEADD после преобразования:

SELECT  DATEADD(days, -2, convert(datetime, [Actual_Termination_Date], 103))

У вас просто есть несовместимый формат даты в вашей колонке, что ужасно.

Неправильный тип данных приводит к этому, поэтому так важно иметь правильные типы данных для столбцов.

Давайте исследуем это немного:

-- some test data
declare @tbl table (dt varchar(20));
insert into @tbl values 
(NULL),
(ཉ/04/2017'),
(ཊ/04/2017'),
(ཌྷ/04/2017'),
(ཏ/04/2017'),
(ད/04/2017'),
(ན/04/2017'),
(པ/04/2017'),
(བ/04/2017'),
(མ/04/2017'),
(ཙ/03/2017'),
(ཙ/04/2017'),
(ཚ/04/2017'),
(ཛ/03/2017'),
(�'),
(�'),
(�'),
(�'),
(�'),
(�');
-- here we handle one format
select convert(date, dt, 103) from @tbl
where len(dt) > 5
or dt is null
-- here we handle excel like format
select dateadd(day, cast(dt as int), 񟫜-01-01') from @tbl
where len(dt) = 5

Итак, как вы видите, вы должны применять различные подходы для этой задачи. CASE WHEN оператор должен хорошо вписаться, см. Ниже SELECT :

select case when len(dt) = 5 then
         dateadd(day, cast(dt as int), 񟫜-01-01')
       else convert(date, dt, 103) end
from @tbl

Есть идеи?

10000