Поля DateTime из SQL Server отображаются неправильно в Excel

Бесчисленное количество раз в течение дня я копирую и вставляю записи из SQL Server Management Studio в Excel.

Моя проблема в том, что значение DateTime, такое как 8/23/2013 4:51:02 PM, отображается неправильно, как показано на изображении ниже, даже если оно правильно отображается в функциональном блоке.

Изменение типа данных на ShortDate устраняет проблемы с отображением, но это утомительно, поскольку у меня есть много полей даты для форматирования. Поскольку Excel и SQL Server являются продуктами Microsoft, можно ожидать, что Excel сможет правильно отобразить поле даты.

EDIT: Таким образом, это выглядит как проблема отображения с Excel. Я скопировал результаты моей Management Studio в "Блокнот" и оттуда в Excel, и я все еще получаю ту же проблему. Копирование 2013-08-23 16:52:11.493 из Блокнота в Excel отобразит 52:11.5 (отображение минут, секунд и округлых миллисекунд). Версия Excel - 2010.

Любые идеи, чтобы упростить это?

Ответ 1

У меня также возникла проблема с этой проблемой, просто скопируйте и вставив поля DATETIME из SQL Management Studio в Excel для манипуляций. Excel имеет правильное значение DATETIME (даже если форматирование применяется после вставки), но по умолчанию не имеет встроенного формата для отображения SQL DATETIME. Здесь исправление:

Щелкните правой кнопкой мыши ячейку и выберите "Формат ячеек". Выберите "Пользовательский". В поле ввода Type: введите

yyyy-mm-dd hh:mm:ss.000

Ссылка: http://office.microsoft.com/en-us/excel-help/create-a-custom-number-format-HP010342372.aspx

Ответ 2

У меня была такая же проблема, как у Андре. Кажется, что нет прямого решения, но CAST в запросе, который генерирует данные, устраняет проблему, если вы можете жить в рамках ограничений SMALLDATETIME. В следующем запросе первый столбец неправильно форматируется в Excel, второй делает.

SELECT GETDATE(), CAST (GETDATE() AS SMALLDATETIME)

Возможно, дробная часть секунд в DATETIME и DATETIME2 смущает Excel.

Ответ 3

Хотя не полный ответ на ваш вопрос, в Excel есть клавиши быстрого доступа, чтобы изменить форматирование выбранной ячейки (я) на дату или время (к сожалению, я не нашел ее для Date + Time).

Итак, если вы просто ищете даты, вы можете выполнить следующее:

Диапазон копирования из SQL Server Management Studio
Вставить в Excel
Выберите диапазон ячеек, который нужно отформатировать как Даты
Нажмите Ctrl + Shift + 3

Для форматирования как Times используйте Ctrl + Shift + 2.

Вы можете использовать это в SQL SERVER

SELECT CONVERT (nvarchar (19), ColumnName, 121) AS [Изменено] FROM Таблица

Ответ 4

Вот хак, который может быть полезен... он ставит апостроф перед значением времени, поэтому, когда вы щелкаете правой кнопкой мыши на выходе в SSMS и говорите "Копировать с заголовками", а затем вставляете в Excel, он сохраняет миллисекунды/наносекунды для значений datetime2. Это немного уродливо, что он ставит там апостроф, но это лучше, чем разочарование в работе с Excel, делающей нежелательное округление по значению времени. Дата - это формат в Великобритании, но вы можете посмотреть страницу функции CONVERT в MSDN.

SELECT CONVERT (VARCHAR (23), sm.MilestoneDate, 103) AS MilestoneDate, '' '' + CONVERT (VARCHAR (23), sm.MilestoneDate, 114) AS MilestoneTime FROM SomeTable sm

Ответ 5

Найдите решение, которое не требует запоминания и повторного набора пользовательского формата datetime yyyy-mm-dd hh:mm:ss.000

В новой ячейке напишите либо =NOW(), либо любую действительную дату + время, например 5/30/2017 17:35: оно будет правильно отображаться на вашем языке, например. 5/30/2017 5:35:00 PM
Выберите ячейку, щелкните значок Формат Painter (кисть)
Теперь нажмите на заголовок строки столбца, который вы хотите применить к формату.

Это скопирует правильный формат даты и времени во весь столбец, чтобы он отображался правильно.

Ответ 6

Попробуйте следующее: Вставьте "2004-06-01 00: 00: 00.000" в Excel.

Теперь попробуйте вставьте "2004-06-01 00:00:00" в Excel.

Excel, похоже, не может обрабатывать миллисекунды при вставке...

Ответ 7

У меня была такая же проблема некоторое время, когда я генерирую довольно много специальных отчетов из SQL Server и копирую их в Excel. Я также приветствовал бы правильное решение, но моим временным решением было создание макроса по умолчанию в Excel, который преобразует выделенные ячейки в формат даты и времени Excel и назначает его горячей клавише (Shift-Ctrl-D в моем случае). Поэтому я открываю Excel, копирую/вставляю из SSMS в новый рабочий лист Excel, выделяю колонку для преобразования и нажимаю Shift-Ctrl-D. Выполнение задания.