sql расчеты по varchar(5) [чч]: мм

Question

Не могли бы вы помочь мне здесь, пожалуйста?

У меня есть столбец со значениями varchar(5), например, [чч]: мм, например. 7:30, 13:15, 10:45, 9:00 и т. Д. Я использую datediff() для вычисления разницы во времени между этими значениями. Все идет гладко, пока не появится значение больше 23:59. Я пытался преобразовать столбец во время, дату и так далее, но каждый раз получал один и тот же результат -> значение "вне диапазона". Попробовал преобразовать в десятичную без удачи.. пробовал dateadd(час,0,@ значение)... У меня нет идей. Пожалуйста посоветуй.

Я на MS SQL 2012

ОБНОВЛЕНИЕ- извините, ребята, я не прояснил себе проблему. Когда я говорил время, я имел в виду продолжительность (а не конкретное время в течение дня). Я храню время, потраченное на какую-то задачу. Это может быть 1 час, но может быть даже 100 часов (скажем, в течение месяца). В Excel я бы использовал [чч]: мм формат iso чч: мм, чтобы получить это. [Чч]: мм из Excel - это именно то, что я ищу в SQL, но не могу его найти. Является ли varchar(5) подходящим форматом для хранения таких данных?

Answer 1

Чтобы преобразовать (не время) значения в формате ЧЧ: ММ, извлеките часы, преобразуйте в минуты, затем добавьте минуты;

cast(left(fld, charindex(':', fld) - 1) as int) * 60 + cast(substring(fld, charindex(':', fld) + 1, len(fld)) as int)

Затем вы можете сравнить их напрямую или добавить их к дате / времени использования. dateadd с minutes флаг.

Answer 2

Я думаю, что попытка преобразовать значения, отличные от времени, во время угадывания того, что имел в виду пользователь, когда он / она вводил данные, может привести к неверным данным и неверным результатам.

Нет данных лучше, чем неверные данные. Поэтому я хотел бы проверить значения, если это действительное значение времени, принять разницу, иначе просто проигнорировать его.

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

Тестовые данные

Представьте, что у вас есть две колонки StartTime а также EndTime оба VARCHAR(5) тип данных.

Теперь некоторые значения являются недействительными значениями времени

DECLARE @TABLE TABLE (StartTime VARCHAR(5), EndTime VARCHAR(5))

INSERT INTO @TABLE VALUES 
('09:00' , '15:00'),
('10:00' , '17:00'),
('15:00' , '24:30'),     --<-- Invalid Endtime
('25:00' , '15:00')      --<-- Invalid StartTime

запрос

С помощью ISDATE() Функция принимает разницу времени начала и окончания только тогда, когда оба значения являются действительными значениями времени, в противном случае они просто игнорируются.

ISDATE() вернуть 1, если переданное значение является действительным DATETIME, DATEили же TIME и т. д. и 0, если это не так.

SELECT CASE 
        WHEN  ISDATE(StartTime) = 1 AND ISDATE(EndTime) = 1
        THEN DATEDIFF(HOUR, StartTime, EndTime)
       END
FROM @TABLE 

Набор результатов

╔═════════════════╗
║ Datediff_Result ║
╠═════════════════╣
║ 6               ║
║ 7               ║
║ NULL            ║
║ NULL            ║
╚═════════════════╝