Как сравнить DATETIME с переменной date, которые могут иметь разную длину?

ProgramBox

Как сравнить DATETIME с переменной date, которые могут иметь разную длину?

Post author:admin
Запись опубликована:3 апреля, 2023
Post category:Вопросы по программированию

#sql-server-2008

Вопрос:

В SQL Server 2008 R2 мне нужно сравнить две даты рождения из разных таблиц.

столбец C.date_of_birth имеет тип DATETIME
столбец R.dob — это дата, сохраненная как VARCHAR , но без начальных нулей в частях месяца или дня, чтобы сделать ее длину согласованной (<- это сложная часть).

Я приблизился к этому:

 where R.dob <> convert(varchar, cast(C.date_of_birth as DATE), 101)

но это возвращает слишком много строк, потому что 1/5/1923 in R.dob не соответствует 01/05/1923 in C.date_of_birth .

Как я могу отформатировать, R.dob чтобы его можно было правильно сравнить с C.date_of_birth ?

Спасибо.

1. почему datetime хранится как varchar? Если вы можете, устраните причину, а не симптомы.

2. … в противном случае преобразуйте переменную в date (не наоборот), а затем сравните две даты.

3. И каков правильный синтаксис преобразования varchar в date? Я перепробовал все, что мог придумать, и большинство выдает ошибку «Ошибка преобразования при преобразовании даты и / или времени из символьной строки». Я пробовал CAST (R.dob как DATETIME2), CONVERT (datetime, R.dob, 101) и любую другую комбинацию, о которой я могу думать. Извините — долгий день.

4. @user477526: правильный синтаксис был бы CAST(r.dob AS DATE) , если вас интересует только часть даты (не время). ОДНАКО: большая проблема, которую я вижу, заключается в том, что ваше R.dob представление даты зависит от языковых / региональных настроек — и это просто ад. Я согласен с Митчем: вы должны исправить корневую проблему и сделать это DATE столбцом — иначе вы будете бороться и дергать себя за волосы до конца своих дней….

Ответ №1:

Вы можете использовать свой varchar в качестве datetime. Например, результат этого:

 select cast('1/5/1923' as datetime)

Было бы 1923-01-05 00:00:00.000

Затем вы можете просто использовать DATEDIFF() для сравнения их, чтобы увидеть, какие из них равны дню (или любому желаемому интервалу).

Ответ №2:

Как насчет этого?

 declare @date_of_birth datetime = '1923-01-05',
        @dob varchar(10) = '01/05/1923'

select  @date_of_birth,
        CONVERT(datetime, @dob, 101), -- 101: mm/dd/yyyy
        case    when @date_of_birth = CONVERT(datetime, @dob, 111)
                then 1 else 0
        end result

1. Спасибо, Рубенс. Я попытался ПРЕОБРАЗОВАТЬ (datetime, R.dob, 101), но выдает ошибку «Ошибка преобразования при преобразовании даты и / или времени из символьной строки». Кстати, R.dob хранится как VARCHAR(4096).

Ответ №3:

Я бы попытался привести поле varchar к дате и выполнить сравнение; например, все эти форматы можно легко преобразовать в тип даты:

 SELECT cast ( '1/1/2011' as date) col1
union
SELECT cast ( '01/11/2011' as date) col1
union
SELECT cast ( 'Oct 7 2011' as date) col1
union
SELECT cast ( '2011-02-23' as date) col1

Выводит:

 col1
----------
2011-01-01
2011-01-11
2011-02-23
2011-10-07

Итак, в вашем запросе вы можете сравнивать даты с датами.

Ответ №4:

Как говорили другие, первым вариантом было бы изменить тип данных R.Dob на Date .

В противном случае вы должны привести свое значение varchar к date , и в своих комментариях вы получите ошибку при этом. Причина этого, вероятно, в том, что у вас недопустимые даты в столбце. Вы могли бы использовать функцию isdate, чтобы выяснить, какие строки имеют недопустимые даты.

Другой причиной может быть то, что ваш SQL Server не знает, как интерпретировать ваши строки даты. Я не знаю, как интерпретировать это значение 1/5/1923 так откуда SQL Server мог знать?

Используйте set dateformat, чтобы указать SQL Server, как интерпретировать строку даты.

 set dateformat mdy
select cast('1/5/1923' as date)

set dateformat dmy
select cast('1/5/1923' as date)

Результат:

 ----------
1923-01-05

----------
1923-05-01