我正在尝试将nvarchar形式的记录转换为时间。例如:1520
变成15:20:0000
但是它抛出错误:从字符串转换日期和/或时间时转换失败。
因为数据集中有错误的时间戳,比如values:
4059','7054','4054','4030','3040','6046','7555','8030', '1169'
我正在使用查询:
select [QHHHMM],
cast(SUBSTRING([QHHHMM], 1, 2) + ':' + SUBSTRING([QHHHMM], 3, 2) as time) as TimeHistory
FROM [MOCK124].[dbo].[F2QH]
where
[QHHHMM] not in ('4059','7054','4054','4030','3040','6046','7555','8030')
and [QHHHMM] not in ('1169')
但是我仍然看到有更多的记录是无效的时间。谁能帮助应该是哪里子句排除所有无效的时间戳?
谢谢
将小时/分钟视为整数?
where cast(left(QHHHMM, 2) as int) between 0 and 23
and cast(right(QHHHMM, 2) as int) between 0 and 59
算出来了,我们可以用在哪里[qhhhmm]> '2359'或substring([QHHHMM], 3,2)> '59'
谢谢!