Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 обработать неконвертируемую строку  [new]
проблемчик
Guest
имеется таблица примерно такого вида
declare @t table (id int not null, r varchar(21) not null);
insert into @t(id, r) values 
(343,   '1028-050531'),
(343,   '2591-041231'),
(333,	'000000000003300000077'),	
(333,	'050000810010106689000'),	
(333,	'F100077710075273'),	
(571,	'2431-31/07/2001'),
(703,	'001')	

ее трогать нельзя,
получить результат надо только запросом.
в "супер-номере" r "заложена" дата.
ну т.е. это так клиент утверждает, что у них последние 6 символов в r
представляют собой дату формата 12.

разумеется, запрос
select id, r, RIGHT(r,6) as dt_str,
       convert(char(10), CONVERT(datetime, RIGHT(r,6), 12), 103) as dt        
from @t

свалился на
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

ибо имеются "неконвертируемые" строки см. таблицу-пример.
причем зоопарк полный: могут быть строки в 1 или 3 символа,
могут быть цифры, но это никакая не дата,
могут быть даты с разделителями.

клиент желает получить столбец-"дату" (char(10) они хотят, именуя это "датой") в формате 103,
где это возможно. где невозможно, NULL.
и желательно столбец- "строку" где мусор, откуда бы они вручную, если особо надо,
выколупали бы дату. у меня было сделано RIGHT(r,6) as dt_str, но видимо туда надо запихать RIGHT(r,10).

возможно ли как-то это осуществить?
в смысле, не фильтруя(строки должны быть все)
частично отконвертировать, где это возможно, и чтоб не "падал" запрос
30 окт 13, 12:42    [15051856]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
Гость333
Member

Откуда:
Сообщений: 3683
проблемчик
частично отконвертировать, где это возможно, и чтоб не "падал" запрос

select id, r, RIGHT(r,6) as dt_str,
       convert(char(10), TRY_CONVERT(datetime, RIGHT(r,6), 12), 103) as dt
from @t
30 окт 13, 12:54    [15051966]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
проблемчик
Guest
Гость333,

это что-то из 2012?
спасибо, но надо под 2008 R2.
т.е. case.
у iap-а были изыскания на тему "продвинутый isnumeric",
но тут мало, что надо цифры.
надо, чтоб их комбинация представляла валидную дату
30 окт 13, 13:04    [15052064]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
проблемчик,

Т.к. формат 12 короткий, то, исходя из предположения, что все даты лежат в этом веке, можно попробовать так:
declare @t table (id int not null, r varchar(21) not null);
insert into @t(id, r) values 
(343,   '1028-050531'),
(343,   '2591-041231'),
(333,	'000000000003300000077'),	
(333,	'050000810010106689000'),	
(333,	'F100077710075273'),	
(571,	'2431-31/07/2001'),
(703,	'001')

;with cte as (
  select id, r, '20'+SUBSTRING(RIGHT(t.r,6),1,2)+'-'+SUBSTRING(RIGHT(t.r,6),3,2)+'-'+SUBSTRING(RIGHT(t.r,6),5,2) rr
  from @t t
)
select id, r, CAST('' as xml).value('sql:column("rr") cast as xs:date ?', 'date') from cte

(выходной формат для date - YYYY-MM-DD)
30 окт 13, 13:15    [15052163]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> надо, чтоб их комбинация представляла валидную дату

ну так, isdate тогда. единственно, она от dateformat зависит, но можно предварительно составить yyyymmdd.
30 окт 13, 13:22    [15052224]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
проблемчик
Guest
Сон Веры Павловны,

и тут облом.
минимум из удавшихся конвертов это 1949/12/31
а за вариант спасибо, мне себе пригодится для общего развития
30 окт 13, 13:22    [15052229]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
проблемчик
Guest
Сон Веры Павловны,
какая прелесть, только благодаря Вам до меня дошло, что их 1949, 1950 и далее по списку у меня конвертилось в 2049, 2050, ...

daw,
спасибо. сейчас попробую
30 окт 13, 13:28    [15052271]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
проблемчик,

Можете написать доп. обработку для столетия - когда, скажем, количество лет в столетии (2 цифры) больше соответствующего текущей дате, то это 19, когда меньше или равно - 20.
С set dateformat и isdate результат получится аналогичным приведенному мной. Хотя этот вариант, я думаю, будет работать быстрее.
30 окт 13, 13:34    [15052328]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
kirser
Member

Откуда: Киев
Сообщений: 225
автор
выколупали бы дату. у меня было сделано RIGHT(r,6) as dt_str, но видимо туда надо запихать RIGHT(r,10).

У вас в одном случае 6 символов, а в другом - 10:
(343,   '1028-050531'),
...	
(571,	'2431-31/07/2001'), 

Может, лучше было бы "плясать" от знака "-" и до конца строки?
К тому же, формат даты в полученном результате у вас разный. Это можно решить через функцию. Заодно и валидацию на ISDATE сделаете. Кстати, ISDATE иногда лажает, выдает TRUE на строку, в которой значение не является датой. В функе можно все варианты фармата дат перебрать и выдать "на гора" нужный формат.
30 окт 13, 14:01    [15052520]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Кстати, ISDATE иногда лажает, выдает TRUE на строку, в которой значение не является датой.

а примерчик? про зависимость от dateformat уже сказано.
30 окт 13, 14:06    [15052560]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
o-o
Guest
всем спасибо.
пока работает вот такое:

declare @t table (id int not null, r varchar(21) not null);
insert into @t(id, r) values 
(343,   '1028-491231'),
(343,   '1028-500101'),
(343,   '2591-041231'),
(333,	'000000000003300000077'),	
(333,	'050000810010106689000'),	
(333,	'F100077710075273'),	
(571,	'2431-31/07/2001'),
(703,	'001'),
(1,     '000000000002380100756')

select id, r, right(r,10), 
       case when right(r,6) = '491231' then '31/12/1949'
            when ISDATE(right(r,6)) = 1 then convert(char(10), CONVERT(datetime, RIGHT(r,6), 12), 103)
            when ISDATE(right(r,4) + left(right(r,7),2) + left(right(r,10),2)) = 1
            then convert(char(10), CONVERT(datetime, right(r,4) + left(right(r,7),2) + left(right(r,10),2), 12), 103)  
       end as dt           
from @t


проблема со столетием вылезла только с 19491231 вида 491231 (-> 31/12/2049),
откуда у меня взялось впечатление, что и 1950 не в то столетие конвертилось???
наверное, это был запущен запрос от Сон Веры Павловны, и кто-то не туда смотрел
по крайней мере, запрос не валится.
30 окт 13, 14:12    [15052608]     Ответить | Цитировать Сообщить модератору
 Re: обработать неконвертируемую строку  [new]
проблемчик
Guest
ок, последняя(надеюсь!) поправочка.
надо еще наличие слэша проверять, в случае 10ти символов, иначе повылазили вещи типа
000000000000100426661 --> 01/04/6661

select id, r, right(r,10), 
       case when right(r,6) = '491231' then '31/12/1949'
            when ISDATE(right(r,6)) = 1 then convert(char(10), CONVERT(datetime, RIGHT(r,6), 12), 103)
            when ISDATE(right(r,4) + left(right(r,7),2) + left(right(r,10),2)) = 1 
                 and (left(right(r,5),1) = '/' and left(right(r,8),1) = '/')
            then convert(char(10), CONVERT(datetime, right(r,4) + left(right(r,7),2) + left(right(r,10),2), 12), 103)  
       end as dt           
from @t
30 окт 13, 14:42    [15052899]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить