Member 12966735 Ответов: 4

Как преобразовать строку в datetime в SQL server


У меня есть столбец даты, который является varchar(50), и он содержит смешанные даты, такие как dd/mm/yyyy и mm/dd/yyyy теперь я хочу преобразовать значения столбца даты в smalldatetime
но это дает ошибку
Conversion failed when converting date and/or time from character string. for mm/dd/yyyy 
please give some suggestion so that i can convert this mixed data in smalldatetime  format
Thank you in advance.


Что я уже пробовал:

SELECT Convert(date,'01/14/2017',103)


declare  @tbl table
(
date varchar(20)
)


insert into @tbl
select '14/03/2017'

insert into @tbl
select '01/14/2017'


select convert(smalldatetime ,convert(varchar(10),date,103), 103) from @tbl

select convert(smalldatetime,convert(varchar(10),date,112),103) from @tbl

CHill60

Ты не можешь. Это "03/11/2016" - 11 марта или 3 ноября?

Paw Jershauge

Как вы вообще узнаете, из какого стиля следует перейти ???

4 Ответов

Рейтинг:
32

CHill60

Предположим, что эта таблица находится под вашим контролем, и вы пытаетесь сделать какую-то домашнюю работу или что-то подобное ... Никогда не храните даты в текстовых столбцах (char, nchar, varchar, nvarchar или text). Всегда храните даты в Столбцах типов DATE или DATETIME Оставьте любое форматирование данных даты на уровне пользовательского интерфейса, а не при извлечении из базы данных.

Если вы застряли с этими данными, то у вас есть проблемы с определением того, какой формат использовать ... это будет хорошо для любых дат после 12-го числа любого месяца, потому что формат DD/MM/YYYY или MM/DD/YYYY будет очевиден (нет никакого 13-го месяца, поэтому везде, где появляется это значение >12, должно быть DAY о дате. Возможно, вы сможете обработать большую часть этих данных с помощью WHILE цикл, оставляя неоднозначные данные для обработки вручную


Рейтинг:
2

Paw Jershauge

Это не то, что я бы рекомендовал, но это должно работать для двух возможных комбинаций, которые вы указали

declare  @tbl table
(
	[date] varchar(20)
)
 

insert into @tbl
select '14/03/2017'
UNION ALL
select '01/14/2017'
 

SELECT CASE WHEN TRY_PARSE([date] AS smalldatetime) IS NULL THEN TRY_PARSE((SUBSTRING([date],4,2) + '/' + SUBSTRING([date],1,2) + '/' +SUBSTRING([date],7,4)) AS smalldatetime) ELSE TRY_PARSE([date] AS smalldatetime) END [convertedtype] FROM @tbl


Результат:
convertedtype
2017-03-14 00:00:00
2017-01-14 00:00:00


CHill60

Согласно комментариям в моем решении, он потенциально будет неверно истолковывать даты - попробуйте добавить

UNION ALL select '03/11/2017'
чтобы таблицу заселить - она выходит как 11 марта, но дата здесь 3 ноября.

Paw Jershauge

@CHill60, я думаю, что сказал, что не рекомендовал бы его; но если первый бросок провалится, я попробую что-то еще, я не говорю, что это правильно...

CHill60

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

Рейтинг:
1

vinuvasahanponniah

привет мое решение таково

Выберите конвертировать(datetime,'2017/05/05',103)

2017-05-05 00:00:00.000


Выберите конвертировать(дата,cast('2017/05/05' as date),103)

2017-05-05

Выберите конвертировать(дата и время,'05/05/2017',103)

2017-05-05 00:00:00.000


Выберите конвертировать(дата,cast('05/05/2017' as date),103)

2017-05-05


SELECT Convert(smalldatetime,'2017/05/05',103)

2017-05-05 00:00:00.000


SELECT Convert(smalldatetime,cast('2017/05/05' as date),103)

2017-05-05 00:00:00.000


CHill60

Отлично подходит для этой даты, не так ли? Теперь попробуем 03/11/2017' ... это 11 марта или 3 ноября? - Вы уверены?

vinuvasahanponniah

нет, подожди

Рейтинг:
1

Jeff Moden

Нет... это все неправильно. Если у вас есть колонка, содержащая смесь ММ/ДД/гггг и ДД/ММ/гггг, вы абсолютно никак не сможете определить, является ли что-то вроде 7/6/2000 7 июня или 6 июля только из информации в этой колонке. Вы должны поговорить с тем, кто предоставил вам данные, чтобы заставить их определиться с форматом дат. Было бы лучше, если бы вы использовали формат ISO YYYYMMDD. Не соглашайтесь на гггг-ММ-ДД, потому что если это для французского языка, то он приходит как гггг-ДД-мм.


CHill60

Я указал на проблему с определенными датами 3 года назад.
Что касается "формата даты", то я с этим совершенно не согласен. ISO 8601 допускает разделитель" -", на самом деле, если для обозначения даты используются только год и месяц, то" -" должен использовать. Я обрабатывал даты для многих культурных сред и никогда не сталкивался с тем, что YYY-MM-DD неправильно интерпретируется для любого языка. Я с удовольствием исправлюсь, если вы сможете предоставить доказательства.
Однако даты должны храниться в виде дат, а не строк, тогда нет понятия "формат".

Jeff Moden

Да... ISO 8601 допускает использование тире в качестве разделителей. И нет... тире не нужно включать в формат YYYYMM, хотя я, конечно, думаю, что они делают его гораздо более читабельным и действительно определяют, что он правильно отформатирован.

Но... ISO 8601 говорит, что тире-это альтернативный формат от формата YYYYMMDD, который является основным форматом. "альтернативные" форматы часто искажаются, и в SQL Server, если язык французский, формат с использованием тире-гггг-ДД-мм.

Реальный ключ здесь заключается в том, что мое предложение использовать YYYYMMDD на самом деле вторично, и OP должен заставить людей согласиться на стандарт, который является полностью однозначным. К сожалению, в SQL Server YYYY-xx-xx не является полностью однозначным.