Convert varchar column to time and use it in a where clause

5jvtdoz2  于 2023-02-28  发布在  其他
关注(0)|答案(1)|浏览(117)

I want to convert a varchar column that has written time information in it. But in an unusual way.

Here a sample of the column dtib

  • Time: 70'000 should be converted to 07:00:00
  • Time: 143'000 should be converted to 14:30:00

I want to make variable on it, so I can use it for example:

where btip between &btibA and &btibB

and if possible with the column type of time .

In the end, I want to create a variable for btiA = 07:00:00 and 15:00:00 and filter all column that fits in this time range.

Can anyone help me?

umuewwlo

umuewwlo1#

You can use as below mention code, but I'll suggest to change table design if possible.

CONVERT(TIME, STUFF(STUFF(RIGHT(REPLACE('00000' + btip, CHAR(39), ''), 6), 3, 0, ':'), 6, 0, ':'))

相关问题