Блог

Главная - Вопросы по программированию - Дата вычисляемого столбца Выпуск Високосного года

Дата вычисляемого столбца Выпуск Високосного года

#sql #sql-server #tsql #calculated-columns #leap-year

Вопрос:

У меня есть таблица имен и связанных с ними дат рождения. Я могу получить имена всех, чей день рождения сегодня, сопоставив даты МЕСЯЦА и ДНЯ между датой рождения в базе данных и текущей датой. Тем не менее, мне нужно составить списки «с нетерпением», скажем, всех дней рождения в ближайшие две недели.

Очевидным решением было бы вычислить столбец для каждого человека, показывающий его/ее «день рождения в этом году». Легко извлечь месяц и день из дня рождения, добавить текущий год и привести всю строку в качестве даты. Таким образом, я мог бы просто получить тех, чей «день рождения в этом году» приходится на X дней от текущей даты. Однако у меня есть один человек с днем рождения 29 февраля, и 29 февраля не каждый год, поэтому вычисляемый столбец «захлебывается», когда я запрашиваю или открываю таблицу со следующей ошибкой:

Преобразование типа данных char в тип данных datetime привело к значению datetime вне диапазона.

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

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

1. 1) Покажите нам ваш текущий вычисляемый столбец. 2) Зачем вам преобразовывать его в строку? Операции с датами всегда должны храниться в виде дат, что решит вашу проблему.

2. Что вы храните в БД ? Как вы конвертируете в настоящее время ?

Ответ №1:

Измените свой запрос с использования convert на использование try_convert , которое не приведет к ошибке в несуществующую дату, а вместо этого вернет значение NULL. Это исключит вашего клиента на день рождения 29 февраля, если рабочий год не является високосным.

Ответ №2:

Ваш подход не сработает в конце декабря, потому что год меняется. Вот другой подход:

  1. Добавьте количество лет к дате, и пусть база данных обрабатывает високосные годы.
  2. Затем проведите сравнение.

Итак, логика первого заключается в следующем:

 select dateadd(year, year(getdate()) - year(dob), dob)

Затем, чтобы узнать даты рождения в ближайшие две недели:

 where dateadd(year, year(getdate()) - year(dob), dob) >= convert(date, getdate()) and
      dateadd(year, year(getdate()) - year(dob), dob) < dateadd(14, day, convert(date, getdate())

Однако это все еще не учитывает даты рождения, которые могут быть в следующем году. Итак, чтобы справиться с концом года, подумайте и об этом:

 where (dateadd(year, year(getdate()) - year(dob), dob) >= convert(date, getdate()) and
       dateadd(year, year(getdate()) - year(dob), dob) < dateadd(14, day, convert(date, getdate())
      ) or
      (dateadd(year, 1   year(getdate()) - year(dob), dob) >= convert(date, getdate()) and
       dateadd(year, 1   year(getdate()) - year(dob), dob) < dateadd(14, day, convert(date, getdate())
      )

Ответ №3:

Для этого — вам нужно рассчитать DOB текущего года и следующую дату рождения человека. Для расчета текущего года даты рождения мы можем использовать простой расчет:

 dateadd(year, datediff(year, DateOfBirth, CurrentDate), DateOfBirth)

Затем — нам нужно рассчитать следующую дату рождения, которая просто добавляет год, если текущий год DOB меньше текущей даты:

 dateadd(year, iif(CurrentDOB < CurrentDate, 1, 0), CurrentDOB)

Теперь — это просто вопрос проверки, находится ли следующий DOB в диапазоне. Вот некоторые примеры данных, чтобы показать, как это объединить.

  --==== Some sample dates of birth - including leap year DOB's
Declare @testData Table (DateOfBirth date);
 Insert Into @testData (DateOfBirth)
 Values ('1992-01-09'), ('2020-02-29'), ('1965-09-30'), ('1984-02-29');

 --==== Test using different dates
Declare @current_date date = '2021-02-28';

 --==== Use CROSS APPLY to calculate current year DOB and Next DOB
 Select *
   From @testData                                                                                       As td
  Cross Apply (Values (dateadd(year, datediff(year, td.DateOfBirth, @current_date), td.DateOfBirth)))   As y(CurrentDOB)
  Cross Apply (Values (dateadd(year, iif(y.CurrentDOB < @current_date, 1, 0), y.CurrentDOB)))           As n(NextDOB)
  Where n.NextDOB <= dateadd(day, 14, @current_date);

Если сегодня 2021-02-28, то будут включены дни рождения, которые выпадают на 29-е число. Если сегодня 2021-03-01, то они не будут включены, потому что они будут рассчитаны как 2022-02-28, то есть не в течение 14 дней.

Метки: