Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 тип данных. varchar to datetine  [new]
it001
Member

Откуда:
Сообщений: 11
Всем привет.
Подскажите пожалуйста: Есть база в mssql, а в ней таблица 'A' c полем 'mydata'. Поле 'mydata' имеет тип varchar. Хочу поменять тип в столбце 'mydata' на тип Дату.

Ошибка:
- Unable to modify table.
Conversion failed when converting date and/or time from character string.
или
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.


Select mydata FROM A;

Результат:
2012-03-20 08:22:10.000
2012-03-20 08:22:11.000
2012-03-20 08:22:13.000
2012-03-20 08:22:25.000
2012-03-20 08:22:44.000
2012-03-20 08:22:47.000
2012-03-20 08:22:56.000
2012-03-20 08:23:04.000

Как поменять и в какой тип?
16 окт 12, 17:06    [13328293]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
tru55
Member

Откуда: СПб
Сообщений: 19789
Не знаю, как в MS SQL, но Oracle разрешает подобную смену типа столбца только в случае, если в столбце нет данных. Поэтому в подобном случае техника такая: значения в столбце копируются куда-то (например, во временную таблицу), сам столбец очищается, меняется тип данных, а потом уже идет заполнение данными.

Модератор: Тема перенесена из форума "Проектирование БД".


Сообщение было отредактировано: 17 окт 12, 11:02
17 окт 12, 09:13    [13330987]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
Glory
Member

Откуда:
Сообщений: 104760
it001
Хочу поменять тип в столбце 'mydata' на тип Дату.

Какой командой ?
17 окт 12, 11:03    [13331754]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
it001
Как поменять и в какой тип?
Преобразуйте данные в правильный строковый формат, независимый от языковых настроек.
Заодно проверьте на допустимые значения.

Потом поменяйте тип (в дизайнере или командой ALTER)
tru55
Не знаю, как в MS SQL, но Oracle разрешает подобную смену типа столбца только в случае, если в столбце нет данных.
В MSSQL можно с данными.
17 окт 12, 11:12    [13331825]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
trew
Member

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

В первой стоке уберите лишний пробел (без этого не отправлялось)
-- D ROP TABLE  #tmp 

CREATE TABLE #tmp 
(mydata varchar(60))

INSERT INTO #tmp( mydata ) VALUES  ( '2012-03-20 08:22:10.000')
INSERT INTO #tmp( mydata ) VALUES  ( '2012-03-50 08:22:10.000')
INSERT INTO #tmp( mydata ) VALUES  ( '2012-03-14 08:18:10.000')

UPDATE #tmp
SET mydata = CASE WHEN ISDATE(mydata) =0 THEN null ELSE mydata END 

ALTER TABLE #tmp
    ALTER COLUMN mydata smalldatetime

-- SELECT mydata +1 AS tdate FROM #tmp AS t
17 окт 12, 11:32    [13331971]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
trew,

Эдак все данные удалятся, а нужно просто тип поменять :-)

Нужно ещё добавить вначале:
set dateformat YMD
17 окт 12, 14:51    [13334373]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
trew
Member

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

Проверяй
SELECT mydata, CASE WHEN ISDATE(mydata) =0 THEN null ELSE mydata END  AS sdate
FROM TABLE_user
ORDER BY CASE WHEN ISDATE(mydata) =0 THEN null ELSE mydata END

Если формат дат не соответствует MS SQL, преобразуй к правильному виду. И проверяй, запрос выше.
Приведи пример, какая дата не преобразуется?
17 окт 12, 15:03    [13334496]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
alexeyvg
Эдак все данные удалятся, а нужно просто тип поменять :-)

А как тогда быть, если в этом поле набиты даты с ошибками?
17 окт 12, 15:05    [13334514]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
it001
Member

Откуда:
Сообщений: 11
trew,
Спасибо сработало! Не проверил исходные данные. Косяк! Вот интересно ISDATE возвращает два значения 1 и 0.

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
ISDATE returns 0 if the expression is a datetime2 value.

У меня остались 6 значений, которые - ISDATE(Моё значение) = 0. после того как я их smalldatetime перевел.
Загадка?
17 окт 12, 16:10    [13335085]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
it001
У меня остались 6 значений, которые - ISDATE(Моё значение) = 0. после того как я их smalldatetime перевел.
Загадка?
Да почему загадка - посмотрите, какие значения не преобразовываются, всё станет понятно.
trew
alexeyvg
Эдак все данные удалятся, а нужно просто тип поменять :-)

А как тогда быть, если в этом поле набиты даты с ошибками?
Я имел в виду, что ваш запрос обнулит даты, даже если они набиты без ошибок, я же написал про set dateformat YMD
17 окт 12, 16:33    [13335362]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
trew
Приведи пример, какая дата не преобразуется?
Все даты, у меня
isdate('2012-03-20 08:22:10.000') = 0
что понятно, потому что 20-го месяца не бывает
17 окт 12, 16:34    [13335370]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
it001
Member

Откуда:
Сообщений: 11
alexeyvg
trew
Приведи пример, какая дата не преобразуется?
Все даты, у меня
isdate('2012-03-20 08:22:10.000') = 0
что понятно, потому что 20-го месяца не бывает



A date
0 NULL
1 2007-08-21 08:14:00
1 2007-08-21 08:15:00
1 2007-08-21 08:16:00
1 2007-08-21 08:18:00
1 2007-08-21 08:19:00
1 2007-08-21 08:20:00
1 2007-08-21 08:21:00

Я так понимаю все сейчас ок. А это день. Ну вот так данные содержаться кто-то так придумал)
17 окт 12, 16:41    [13335428]     Ответить | Цитировать Сообщить модератору
 Re: тип данных. varchar to datetine  [new]
it001
Member

Откуда:
Сообщений: 11
Эти теперь 6 значений равны NULL. Так что если БД больше 0 было, то такой способ не подошел. А у меня всего 6 из 140 тыс.
17 окт 12, 16:57    [13335583]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить