Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
SFlash
Member

Откуда:
Сообщений: 143
Доброго времени суток.

Или я туплю или уже ничего не понимаю.

Есть таблица созданная PerfMon-ом - CounterData (кто интересуется что это, то и так знает, кто нет, то и неважно), одно из полей CounterDateTime - char(24), остальные поля в данном случае не нужны.

select top 1 CounterDateTime from CounterData

выдает

CounterDateTime
------------------------
2015-06-22 13:54:41.296

(1 row(s) affected)

Мне необходимо преобразовать при выборке это поле в ДатуВремя для временного ряда.

Пробуем
select top 1 Convert(DateTime,CounterDateTime,121) from CounterData

получаем
Msg 241, Level 16, State 1, Line 22
Conversion failed when converting date and/or time from character string.


Но если теперь это же значение вписать просто вместо поля напрямую, то все ок.
select top 1 Convert(DateTime,'2015-06-22 13:54:41.296',121) from CounterData 

-----------------------
2015-06-22 13:54:41.297

(1 row(s) affected)

Вроде перерыл все что можно, но не нашел ни одного упоминания данной проблемы.
Формат стиля 121 выбрал исходя из MSDN
121 - Канонический формат ODBC (с миллисекундами) - гггг-мм-дд чч:ми:сс.ммм (24-часовой формат)

Есть ли предположения как решить проблему?

Версия SQL:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
22 июн 15, 14:53    [17802291]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
SFlash, версия сервера какая? Если позволяет, то пробуйте TRY_CONVERT, если нет - вам надо найти запись, в которой значение этого поля не соответствует нужному формату, и каким либо образом исключить его.
22 июн 15, 14:57    [17802311]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
SFlash, блин, версию же вы указали :)

Тогда TRY_CONVERT вам в руки:

https://msdn.microsoft.com/en-us/library/hh230993(v=sql.110).aspx
22 июн 15, 14:58    [17802319]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
SFlash,

хвостовой пробел не мешает? У Вас же реально 23 символа, а не 24.
Вам нужен RTRIM(). Издержки типа CHAR() вместо VARCHAR()
22 июн 15, 15:00    [17802328]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
SFlash
Member

Откуда:
Сообщений: 143
select top 1 Convert(DateTime,RTRIM(CounterDateTime),121) from CounterData

Msg 241, Level 16, State 1, Line 22
Conversion failed when converting date and/or time from character string.


Не помогает

Фокус с TRY_Convert не прошел, возвращает NULL.
22 июн 15, 15:10    [17802382]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
SFlash, для всех записей null возвращает? Если сделать select ... where try_convert... is not null - ноль записей вернет?
22 июн 15, 15:11    [17802387]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
SFlash
Member

Откуда:
Сообщений: 143
Ну внимательней читайте. В данном случае работаю с одной записью. (top 1)
Видно что дата корректна, но не конвертируется, а виде текста та же самая дата нормально.
22 июн 15, 15:19    [17802418]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
SFlash
Ну внимательней читайте. В данном случае работаю с одной записью. (top 1)
Видно что дата корректна, но не конвертируется, а виде текста та же самая дата нормально.
Что вернёт
select top 1 QUOTENAME(CounterDateTime) from CounterData
??
22 июн 15, 15:24    [17802432]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
o-o
Guest
может там пробел не тот, а другой(непрерывный) или еще что?
выведите это текстовое значение в бинарном виде, посмотрим, может там левый символ есть
22 июн 15, 15:25    [17802439]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
SFlash
Member

Откуда:
Сообщений: 143
iap
SFlash
Ну внимательней читайте. В данном случае работаю с одной записью. (top 1)
Видно что дата корректна, но не конвертируется, а виде текста та же самая дата нормально.
Что вернёт
select top 1 QUOTENAME(CounterDateTime) from CounterData
??


Null
22 июн 15, 15:26    [17802447]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
VGalamakh
Member

Откуда: Киев (Альба)
Сообщений: 66
а так?

select top 1 Convert(DateTime,CounterDateTime,121) from CounterData
where CounterDateTime = '2015-06-22 13:54:41.296'
22 июн 15, 15:30    [17802461]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
SFlash
Member

Откуда:
Сообщений: 143
select top 1 CounterDateTime from CounterData
where CounterDateTime = '2015-06-22 13:54:41.296'

select top 1 Convert(DateTime,CounterDateTime,121) from CounterData
where CounterDateTime = '2015-06-22 13:54:41.296'



CounterDateTime
------------------------
2015-06-22 13:54:41.296

(1 row(s) affected)


-----------------------
Msg 241, Level 16, State 1, Line 34
Conversion failed when converting date and/or time from character string.
22 июн 15, 15:34    [17802487]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
Там похоже на конце символ с кодом "0".
Вот наглядное воспроизведение вашей проблемы:

select Convert(DateTime,cast('2015-06-22 13:54:41.296'+char(0) as char(24)),121) 
where cast('2015-06-22 13:54:41.296'+char(0) as char(24))='2015-06-22 13:54:41.296'


Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
22 июн 15, 15:40    [17802506]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
VGalamakh
Member

Откуда: Киев (Альба)
Сообщений: 66
а так?
select top 1 Convert(DateTime,substring(CounterDateTime,1,10),121) from CounterData
where CounterDateTime = '2015-06-22 13:54:41.296'

select top 1 Convert(DateTime,substring(CounterDateTime,1,23),121) from CounterData
where CounterDateTime = '2015-06-22 13:54:41.296'
22 июн 15, 15:40    [17802508]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
o-o
Guest
SFlash
Ну внимательней читайте. В данном случае работаю с одной записью. (top 1)
Видно что дата корректна, но не конвертируется, а виде текста та же самая дата нормально.

ниче и не видно,
он может сначала сконвертировать, а потом применить топ или where.
жаба давит на всей таблице TRY_CONVERT прогнать?
не на этой записи спотыкается
22 июн 15, 15:42    [17802511]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
SFlash, не поленился, сделал себе базу с репро. действительно, на конце char(0).

Только replace'ом не меняется.

А вот так работает:

select convert(datetime, left(CounterDateTime, 23), 121)
from [PerfMonTest].[dbo].[CounterData]
22 июн 15, 15:51    [17802564]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
SFlash, чтобы натолкнуть вас на мысль, как решать подобные проблемы, дам такой скрипт:

SELECT number, substring(CounterDateTime, number, 1), ascii(substring(CounterDateTime, number, 1))
FROM	[PerfMonTest].[dbo].[CounterData]
		inner join master.dbo.spt_values sv
				on sv.type = 'P' 
				and sv.number > 0 
				and sv.number <= len(CounterDateTime)
22 июн 15, 15:53    [17802571]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
SFlash
Member

Откуда:
Сообщений: 143
Minamoto
SFlash, не поленился, сделал себе базу с репро. действительно, на конце char(0).

Только replace'ом не меняется.

А вот так работает:

select convert(datetime, left(CounterDateTime, 23), 121)
from [PerfMonTest].[dbo].[CounterData]


Во спасиб, точно пашет, поубивал бы разрабов из мелкософта за 0 в конце ))
22 июн 15, 16:00    [17802597]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
o-o
Guest
не, ну трудно было в бинарном виде показать, правда?
o-o
может там пробел не тот, а другой(непрерывный) или еще что?
выведите это текстовое значение в бинарном виде, посмотрим, может там левый символ есть
22 июн 15, 16:04    [17802620]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
SFlash
Member

Откуда:
Сообщений: 143
o-o
не, ну трудно было в бинарном виде показать, правда?
o-o
может там пробел не тот, а другой(непрерывный) или еще что?
выведите это текстовое значение в бинарном виде, посмотрим, может там левый символ есть


Каюсь, даже не подумал что уже у проверенного годами продукта есть такая бага в виде фичи ))
Ранее делал тоже самое только выгружал в csv и relog-ом уже в SQL гнал, проблем не было вообще, думал тут аналогично, только лишнее звено убрать, а вот оно как оказалось ....
22 июн 15, 16:25    [17802710]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
SFlash, кстати, я тут подумал, почему у меня не сработал replace, поискал, и нашел еще одно решение:

select convert(datetime, replace(CounterDateTime COLLATE Latin1_General_BIN, char(0), ''), 121)
from [PerfMonTest].[dbo].[CounterData]
22 июн 15, 16:58    [17802836]     Ответить | Цитировать Сообщить модератору
 Re: Конвертация Char в DateTime то работает, то не работает при одном и том же значении  [new]
o-o
Guest
Minamoto
SFlash, кстати, я тут подумал, почему у меня не сработал replace, поискал, и нашел еще одно решение:

select convert(datetime, replace(CounterDateTime COLLATE Latin1_General_BIN, char(0), ''), 121)
from [PerfMonTest].[dbo].[CounterData]

можно даже не делать из этого тайну, а в явном виде рассказать,
что в скульном коллэйшене нет проблем с char(0), а в виндовом есть:

REPLACE performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.
0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in REPLACE.

т.е. прокатит и SQL_Latin1_General_CP1_CI_AS :)
22 июн 15, 17:09    [17802892]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить