Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Ошибка при преобразовании даты convert(date,birthday,104)  [new]
BrZ
Member

Откуда:
Сообщений: 10
При преобразовании даты:
UPDATE All_data
SET birthday2 = convert(date,birthday,104)

вылетает ошибка:
Conversion failed when converting date and/or time from character string.

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

В столбце birthday2 значения NULL разрешены.
Предварительно проходил запросом:
UPDATE People
SET Birthday = NULL
WHERE Birthday = 'NULL'
21 фев 15, 21:21    [17297573]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
BrZ
Причём вначале запрос начинает обрабатываться, и дата показывается в нужном формате, но потом видимо когда обработка доходит до определённой строки запрос прерывается с ошибкой.
Надо же, при выполнении UPDATE "дата показывается в нужном формате"!

А собственно, вопрос то ваш в чём?
21 фев 15, 21:24    [17297579]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
BrZ
Member

Откуда:
Сообщений: 10
Предполагаю что в какой-то ячейке есть неподходящий под формат даты текст, но тогда как его найти?
В столбце более миллиона записей
21 фев 15, 21:24    [17297580]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
Glory
Member

Откуда:
Сообщений: 104751
BrZ
но потом видимо когда обработка доходит до определённой строки запрос прерывается с ошибкой.

наверное надо найти эту строку(строки) и исправить в них неправильные значения
21 фев 15, 21:25    [17297582]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
BrZ
Предполагаю что в какой-то ячейке есть неподходящий под формат даты текст, но тогда как его найти?
"Ячейки" какой тип данных имеют? varchar?

Проверьте для начала записи на соответствие шаблону командой LIKE
21 фев 15, 21:27    [17297585]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
BrZ
Member

Откуда:
Сообщений: 10
alexeyvg
BrZ
Причём вначале запрос начинает обрабатываться, и дата показывается в нужном формате, но потом видимо когда обработка доходит до определённой строки запрос прерывается с ошибкой.
Надо же, при выполнении UPDATE "дата показывается в нужном формате"!

А собственно, вопрос то ваш в чём?


Да, ты прав, дата отображается когда делаю селект:
SELECT convert(date,Birthday,104)
FROM people

Вопрос в том, как найти из -за чего возникает ошибка и преобразовать дату из вида

Jul 10 2011 12:00AM

в

2011-07-10
21 фев 15, 21:28    [17297590]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
BrZ
Member

Откуда:
Сообщений: 10
alexeyvg
BrZ
Предполагаю что в какой-то ячейке есть неподходящий под формат даты текст, но тогда как его найти?
"Ячейки" какой тип данных имеют? varchar?

Проверьте для начала записи на соответствие шаблону командой LIKE


Ячейка Birthday имеет тип данных varchar
21 фев 15, 21:29    [17297593]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
BrZ
Вопрос в том, как найти из -за чего возникает ошибка и преобразовать дату из вида

Jul 10 2011 12:00AM

в

2011-07-10
1. Выясните, какой тип данных у поля birthday
2. Выясните, в каком формате туда записаны даты.
3. Приведите даты к какому либо одному формату, если они встречаются в разных форматах
4. Напишите запрос на получение поля birthday, сконвертированного в тип date
5. Если в пункте 4 нет ошибок, выполните update, если есть, начените с п. 1.
21 фев 15, 21:33    [17297603]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
alexeyvg
BrZ
Вопрос в том, как найти из -за чего возникает ошибка и преобразовать дату из вида

Jul 10 2011 12:00AM

в

2011-07-10
1. Выясните, какой тип данных у поля birthday
2. Выясните, в каком формате туда записаны даты.
3. Приведите даты к какому либо одному формату, если они встречаются в разных форматах
4. Напишите запрос на получение поля birthday, сконвертированного в тип date
5. Если в пункте 4 нет ошибок, выполните update, если есть, начените с п. 1.
Или, как вариант, можно вместо п. 3 написать конвертацию из разных форматов, используя оператор CASE

Но начать нужно всё равно с п. 1 (или уже с п.2, раз вы выяснили)
21 фев 15, 21:35    [17297605]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
alexeyvg
1. Выясните, какой тип данных у поля birthday
Да, ещё выясните, какой тип данных у поля birthday2

А то, возможно, вы не понимаете, что делаете.
21 фев 15, 21:37    [17297608]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
o-o
Guest
можно попробовать так отловить:
select *
from People
where left(Birthday, 3) not in
('Jan',
'Feb',
'Mar',
'Apr',
'May',
'Jun',
'Jul',
'Aug',
'Sep',
'Oct',
'Nov',
'Dec')

бывает, что даты строками заносят даже и не руками, и они изначально действительно даты,
только вот язык у заносивших логинов был разный, в результате попадают даты, где месяц совсем не на инглише
21 фев 15, 22:10    [17297689]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
BrZ
Member

Откуда:
Сообщений: 10
А если даты встречаются в формате Aug 13 1962 12:00AM
и в
1977-08-31
то запрос должен работать?
21 фев 15, 23:24    [17297879]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
o-o
Guest
BrZ
А если даты встречаются в формате Aug 13 1962 12:00AM
и в
1977-08-31
то запрос должен работать?


какой именно запрос-то и какие форматы лежат в той таблице?
вот такое работает с перечисленным:
declare @t table (dt varchar(100));
insert into @t values('Jul 10 2011 12:00AM'), ('Aug 13 1962 12:00AM'), ('1977-08-31');

select dt, CAST(dt as date) as dt1
from @t;
---
dt	dt1
Jul 10 2011 12:00AM	2011-07-10
Aug 13 1962 12:00AM	1962-08-13
1977-08-31	1977-08-31

и при чем там вообще 104???
21 фев 15, 23:40    [17297925]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
o-o
Guest
можно примерно оценить имеющийся зоопарк,
покажите результат такого:
select len(Birthday), min(Birthday), max(Birthday)
from People
group by len(Birthday);
21 фев 15, 23:49    [17297957]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
BrZ
Member

Откуда:
Сообщений: 10
101909-01-012011-12-30
19Apr 1 1930 12:00AMSep 30 1999 12:00AM
NULLNULLNULL
21 фев 15, 23:55    [17297981]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
BrZ
Member

Откуда:
Сообщений: 10
Вроде нашел косяк
Выяснил что запрос выдаёт ошибку когда формат данных 1982-01-18
т.к. SELECT convert(date,'1982-01-18',104)
выдаёт как раз эту ошибку Conversion failed when converting date and/or time from character string.
22 фев 15, 00:15    [17298061]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
BrZ
Member

Откуда:
Сообщений: 10
Спасибо тем кто навёл на правильные мысли.
Задача выполнена прогоном 2-х запросов:

UPDATE people
SET birthday2 = convert(date,birthday,104)
where left(Birthday, 3) in
('Jan',
'Feb',
'Mar',
'Apr',
'May',
'Jun',
'Jul',
'Aug',
'Sep',
'Oct',
'Nov',
'Dec')

и

UPDATE people
SET birthday2 = Birthday
where Birthday LIKE '____-__-__'
22 фев 15, 00:26    [17298096]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
o-o
Guest
BrZ
101909-01-012011-12-30
19Apr 1 1930 12:00AMSep 30 1999 12:00AM
NULLNULLNULL

ну так должно было отработать 17297925:
CAST(birthday as date)

откуда вы вообще взяли формат 104, если это dd.mm.yy, т.е. 8 символов?
у вас там строки длины 10 и 19...
22 фев 15, 00:37    [17298119]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка при преобразовании даты convert(date,birthday,104)  [new]
BrZ
Member

Откуда:
Сообщений: 10
Да , точняк. Простой запрос:

UPDATE people
SET Birthday = CAST(Birthday as date)


тоже сработал, просто я до этого его не пробовал :(
спасибо
22 фев 15, 01:34    [17298219]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить