Блог

Главная - Вопросы по программированию - Я хочу преобразовать столбец varchar date в date, чтобы получить промежуточные результаты по дате

Я хочу преобразовать столбец varchar date в date, чтобы получить промежуточные результаты по дате

#sql-server #select

#sql-server #выберите

Вопрос:

Я хочу получить результирующий результат между двумя датами, которые хранятся в моей таблице SQL varchar(50) в yyyy-mm-dd формате. На самом деле, я не знаю, в чем проблема в моем запросе, упомянутом ниже, но он выдает эту ошибку:

Ошибка преобразования при преобразовании даты и / или времени из символьной строки.

Мой запрос SQL select выглядит следующим образом:

 SELECT DISTINCT(bno), 
    name, cdate, ctime, 
    CAST(ISNULL(disc, '0') AS DECIMAL(18, 2)) AS 'Discount', 
    SUM(CASE WHEN ptype = 'Cash' THEN CAST(paid AS DECIMAL(18, 2)) END) AS 'Cash', 
    SUM(CASE WHEN ptype = 'Online' THEN CAST(paid AS DECIMAL(18, 2)) END) AS 'Online', 
    CAST(ISNULL(paid, '0') AS DECIMAL(18, 2)) AS 'GT' 
FROM
    Sales 
WHERE 
    bno != '0' 
    AND CONVERT(date, CONVERT(varchar(50), cdate, 105)) BETWEEN CONVERT(date, convert(varchar(50), '01-09-2020', 105)) 
                                                            AND CONVERT(date, CONVERT(varchar(50), '13-09-2020', 105)) 
GROUP BY
    bno, name, cdate, ctime, disc, paid

Пожалуйста, дайте мне знать, если потребуются какие-либо исправления. Заранее благодарю.

Комментарии:

1. Реальный вопрос в том, почему вы храните даты как a varchar в первую очередь?

2. Также пробелы и разрывы строк важны при написании на любом языке, включая SQL. Потратьте время на форматирование вашего кода.

3. Наконец, похоже, что вы используете оба DISTINCT и GROUP BY здесь. Этот параметр означает, что первый является избыточным, или последний является неполным; вам никогда не понадобятся оба в одном и том же операторе.

4. Вы просто не учитесь на своих предыдущих вопросах. Литералы даты не нужно преобразовывать со стилем, если вы используете однозначный формат. Научитесь делать именно это!

Ответ №1:

На мой взгляд, у вас есть множество проблем.

  1. Ваше поле cdate имеет формат гггг-мм-дд, тогда ваше преобразование в формат не является точным. Как использовать try_cast
  2. Когда вы используете «инструкцию обращения», вы также должны использовать предложение «else»
  3. Ваша «группа по» должна использовать те же поля / поля, что и в вашем предложении select
  4. Distinct всегда избыточны в group by
  5. Используйте try_cast для случаев, когда ваши данные неверны для ожидающего типа
  6. Вы группируетесь по плате в GT, но вы используете платную сумму, а не логику
  7. Я подозреваю, что ваш ctime следует использовать в вашем предложении, где / между (возможно, cdate ctime — это ваша дата)

Попробуйте что-то вроде этого :

 select bno, name, cdate, ctime,
TRY_CAST ( isnull(disc, '0') AS DECIMAL(18,2)) as 'Discount', 
sum(case when ptype = 'Cash' then TRY_CAST(paid AS DECIMAL(18,2)) else null end) as 'Cash', 
sum(case when ptype = 'Online' then TRY_CAST(paid AS DECIMAL(18,2)) else null end) as 'Online', 
sum(TRY_CAST(paid AS DECIMAL(18,2)) as 'GT'
from Sales 
where bno!='0' and 
Try_cast(cdate as date) between '2020-09-01' AND '2020-09-13'
group by bno, name, cdate, ctime, TRY_CAST ( isnull(disc, '0') AS DECIMAL(18,2))

Если вы хотите найти все свои проблемы в ваших данных, вы можете это сделать:

 select bno, name, cdate, ctime, ptype, paid 
from Sales 
where bno!='0' and 
(
Try_cast( cdate as date) is null and cdate<>'' or
case when ptype = 'Cash' then TRY_CAST(paid AS DECIMAL(18,2)) else null end is null and ptype = 'Cash' and paid<>'' or
case when ptype = 'Online' then TRY_CAST(paid AS DECIMAL(18,2)) else null end is null and ptype = 'Online' and paid<>'' or
TRY_CAST ( isnull(disc, '0') AS DECIMAL(18,2)) is null and disc<>'' or
TRY_CAST ( isnull(paid, '0') AS DECIMAL(18,2)) is null and paid<>'' 
)

Ответ №2:

Лучшим подходом было бы применить форматирование к фильтрам между. сохраните тот же формат в поле таблицы, и запрос будет выглядеть примерно так, как показано ниже:

 select
   (bno),
   name,
   cdate,
   ctime,
   CAST(isnull(disc, '0') AS DECIMAL(18, 2)) as 'Discount',
   sum(   case
              when ptype = 'Cash' then
                  CAST(paid AS DECIMAL(18, 2))
          end
      ) as 'Cash',
   sum(   case
              when ptype = 'Online' then
                  CAST(paid AS DECIMAL(18, 2))
          end
      ) as 'Online',
  CAST(IsNULL(paid, '0') AS DECIMAL(18, 2)) as 'GT' from Sales where bno != '0'
  AND convert(date,cdate)
  BETWEEN '01-09-2020' AND '13-09-2020'
  group by bno,name,cdate,ctime,disc,paid

Надеюсь, это поможет.Ключевым моментом является то, что этот оператор (SELECT CONVERT(date,’01-09-2020′)) по умолчанию будет выводиться в этом формате даты (2020-01-09), поэтому, если вы сохраняете тот же формат в поле таблицы, вы можете применить фильтрацию без особых усилий.

Комментарии:

1. Это DISTINCT все еще избыточно здесь, если OPs GROUP BY верен. Следует также отметить, что запрос не будет саргируемым, поэтому не будет использовать какие-либо индексы; это означает, что он может быть очень медленным с большими наборами данных

2. Противоположная проблема форматирования OP. Так много пробелов и разрывов строк, но все равно их трудно читать. И нет необходимости приводить литералы даты, когда они используют однозначный формат. Здесь это делается без стиля и все равно может завершиться неудачей.

3. Спасибо @SMor, я обновил запрос и сосредоточился только на проблеме преобразования даты. Пожалуйста, выполните быстрый тест только с помощью select (все ваши существующие поля) и просто примените приведенные ниже фильтры в предложении where. преобразование (дата, дата cdate) МЕЖДУ ’01-09-2020′ И ’13-09-2020′ Это даст вам представление о том, была ли исправлена проблема с датой.

4. Спасибо @Larnu, я обновил запрос и удалил «DISTINCT», и группировка бесполезна, я просто сосредоточился на проблеме преобразования даты.

Ответ №3:

Причина, по которой вы получаете ошибку, которую вы видите, заключается в том, что вы применяете style параметр CONVERT к преобразованию в varchar , а не к преобразованию в date . Тебе нужно измениться:

 AND CONVERT(date, CONVERT(varchar(50), cdate, 105)) BETWEEN CONVERT(date, convert(varchar(50), '01-09-2020', 105)) 
                                                        AND CONVERT(date, CONVERT(varchar(50), '13-09-2020', 105))

Для:

 AND CONVERT(date, CONVERT(varchar(50), cdate), 105) BETWEEN CONVERT(date, CONVERT(varchar(50), '01-09-2020'), 105)
                                                        AND CONVERT(date, CONVERT(varchar(50), '13-09-2020'), 105)

Обратите внимание, что стиль 105 является dd-mm-yyyy тем, который вы правильно используете в BETWEEN части выражения, но вы упоминаете, что даты в вашей таблице хранятся в yyyy-mm-dd форме, и в этом случае вы должны преобразовать их, используя стиль 23.

Метки: