Проблема с форматированием даты SQL Server

#sql-server

#sql-сервер

Вопрос:

У меня есть база данных SQL Server 2008 с таблицей транзакций. Существует поле, определенное как NVARCHAR(16). Хранимые данные имеют формат даты и времени следующим образом: 2016100708593100

Мне нужно написать запрос, который просматривает это поле и извлекает данные между двумя датами

 select ... from...where convert(varchar,
      convert(datetime,left(a.xact_dati,8)),101) 
      between '9/29/2016' and '10/05/2016'

Я пробовал другие преобразования, но ничего не возвращает никаких данных. Если я использую >=getdate() -1
Я получаю данные, поэтому я должен видеть, что что-то возвращается. Есть предложения?

1. Почему вы сохраняете временные метки как NVARCHAR ? Это действительно плохое решение

2. попробуйте преобразовать формат 112 вместо 101

3. Это база данных поставщика, поэтому я не имею права голоса в том, как хранятся данные. Я попробовал 112, он также не возвращает данных.

4. 10/07/2016 была просто максимальной датой в таблице.

5. ваши даты, выраженные как ‘между ‘29.9/2016′ и ’10/05/2016’, могут быть проблематичными, используйте формат ISO ‘YYYMMDD’, например ‘20161005’

Ответ №1:

Замените предложение WHERE на

 ... WHERE CONVERT(DATETIME,LEFT(a.xact_dati,8)) BETWEEN '9/29/2016' AND '10/05/2016'

Ответ №2:

Предполагая, что порядок YMD просто

 where cast(left('2016100708593100', 8) as date) between '20160929' and '20161005'

Обратите внимание, что это условие не выполняется.

Ответ №3:

Проблема в том, что вы пытаетесь выполнить сравнение даты с данными varchar.

Вы проходите половину пути к преобразованию начального значения в datetime, но затем возвращаетесь к varchar.

Вы можете полагаться на Sql для неявного преобразования параметров between в datetime.

 select 'matched',convert(datetime,left('2016100708593100',8),112)
where convert(datetime,left('2016100708593100',8),112) between '2016/09/29' and '2016/10/10'

Ответ №4:

Возьмите часть даты строки и CAST ее как DATE , а затем сравните ее с правильным форматом даты.

 select ... from... 
where cast(left('2016100708593100', 8) as date)  between '2016-09-29' and '2016-10-10'

Ответ №5:

очень простым и быстрым подходом может быть сохранение этого в BIGINT

Что-то вроде этого:

   cast(left(a.xact_dati,8) as bigint) between 20160929 and 20161005

Но в любом случае это не будет доступно и, следовательно, не может использовать индекс.

Вы можете даже придерживаться varchar, поскольку формат YYYYMMDD безопасен для буквенно-цифровой сортировки. Вырезание строки с LEFT помощью sargable (AFAIK):

   left(a.xact_dati,8) between '20160929' and '20161005'