Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Conversion failed when converting date and/or time from character string. При update.  [new]
СферическийКуб
Member

Откуда:
Сообщений: 142
Добрый день.

Надо проапдейтить одну таблицу Table1 по условиям из другой таблицы-справочника ParamsTable.
В ParamsTable свалка из всяких разных значений разных типов(по логике разных) хранящихся в одном varchar поле param_value

Идентификация типа параметров в справочнике идет по полю PType.
В поле param_value нужного мне типа содержится дата в виде '31/12/2017' остальные значения ненужных мне типов содержат все что угодно, т.е. convert(datetime, param_value, 103) выдаст ошибку.

В итоге такой апдейт
	update t
		set flag = 1	
	
	from	Table1  t    
        inner join  ParamsTable p on  p.tid=t.tid  and p.PType in (1,2,3) 
	where convert(datetime, p.param_value, 103) > @max_date	

выдаст ошибку Conversion failed when converting date and/or time from character string.

Вопрос: почему, и как этого избежать?
В таблице ParamsTable значение поля param_value при условии PType in (1,2,3) заведомо рабочие, и если вывести все значения
нужных типов с преобразованием select convert(datetime, p.param_value, 103) from ParamsTable p where p.tid=t.tid and p.PType in (1,2,3)
то ошибок не будет.

По идее условие в where должно отрабатывать уже после join и мусорных записей другого типа туда не должно попасть.
11 дек 17, 15:05    [21024290]     Ответить | Цитировать Сообщить модератору
 Re: Conversion failed when converting date and/or time from character string. При update.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
СферическийКуб,

автор
По идее условие в where должно отрабатывать уже после join и мусорных записей другого типа туда не должно попасть.

по чьей идее?
TRY_CONVERT/TRY_CAST
11 дек 17, 15:09    [21024303]     Ответить | Цитировать Сообщить модератору
 Re: Conversion failed when converting date and/or time from character string. При update.  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
СферическийКуб
По идее условие в where должно отрабатывать уже после join и мусорных записей другого типа туда не должно попасть.
Совершенно не обязательно. Смотрите план выполнения - там видно в какой момент вычисляется ваш convert.

Исправление "в лоб"
	update t
		set flag = 1	
	
	from	Table1  t    
        inner join  ParamsTable p on  p.tid=t.tid 
	where case when p.PType in (1,2,3) then convert(datetime, p.param_value, 103) end > @max_date
11 дек 17, 15:13    [21024316]     Ответить | Цитировать Сообщить модератору
 Re: Conversion failed when converting date and/or time from character string. При update.  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
это по вашей идее так,
сервер же решил сперва конвертировать.
---
сделайте выборку с нужным условием и без попытки конвертации во временную таблицу.
уже из нее берите и конвертируйте
11 дек 17, 15:13    [21024318]     Ответить | Цитировать Сообщить модератору
 Re: Conversion failed when converting date and/or time from character string. При update.  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
СферическийКуб
По идее условие в where должно отрабатывать уже после join и мусорных записей другого типа туда не должно попасть
Это по вашей идее. А сервер делает вычисления в том порядке, который ему нравится.
Обычный выход из такой ситуации - проверять конвертируемость в CASE.
Другая возможность - если версия сервера SQL2012, то использовать TRY_CONVERT()
WHERE
 CASE
  WHEN p.param_value LIKE '%[0123][0123456789]/[01][012]/[12][0123456789][0123456789][0123456789]%'
  THEN CONVERT(DATETIME,p.param_value,103)
 END>@max_date
Конечно, некоторые "неправильные" даты удовлетворят этому условию...
Но можно усложнять и дорабатывать, а основную массу "правильных" дат отфильтрует без ошибок.

В общем, вы сами себе устроили этот праздник.
Ведь можно же было хранить в строке дату в виде 'YYYYMMDD hh:mm:ss.nnn' или 'YYYY-MM-DDThh:mm:ss.nnn'
11 дек 17, 15:22    [21024349]     Ответить | Цитировать Сообщить модератору
 Re: Conversion failed when converting date and/or time from character string. При update.  [new]
iap
Member

Откуда: Москва
Сообщений: 46978
iap
Ведь можно же было хранить в строке дату в виде 'YYYYMMDD hh:mm:ss.nnn' или 'YYYY-MM-DDThh:mm:ss.nnn'
Хотя, нет. Вам бы это не помогло.
В поле-то хранятся не только даты...
11 дек 17, 15:24    [21024359]     Ответить | Цитировать Сообщить модератору
 Re: Conversion failed when converting date and/or time from character string. При update.  [new]
СферическийКуб
Member

Откуда:
Сообщений: 142
Всем спасибо.
Архитектура базы не моя, я только костыли подставляю.
Сервер 2008R2.
11 дек 17, 15:27    [21024367]     Ответить | Цитировать Сообщить модератору
 Re: Conversion failed when converting date and/or time from character string. При update.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
СферическийКуб,

tryconvert(), если позволяет версия.
11 дек 17, 15:55    [21024493]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить