How to display time in HH:MM format?

Question

How to display a time HH:MM format?

Using SQL 2000

In my Database time column datatype is varchar

Example

Table1

Time

08:00:00
09:00:23
214:23:32

Here I want to take only 08:00, 09:00, 214:23

How to make a query for this condition?

Answer 1

Whilst you could choose to turn the varchar into a datetime and format it there, assuming you do not want rounding, you could could shortcut the process. (Assuming the time format in the varchar is consistent)

select left('08:00:00',5)

Edit : Question altered, now I would use

select substring('243:00:00', 1, len('243:00:00') - 3)

and replace the value I used with the appropriate field

Cheap and cheerful.

Answer 2

I think Andrew was onto a correct solution, just didn't address all of the possibilities:

SELECT LEFT(Time, LEN(TIME)-3)

should trim off the last 3 characters.

Now, if you want to round up, that's another story....