0

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?

SilentGhost
  • 307,395
  • 66
  • 306
  • 293
Gopal
  • 11,712
  • 52
  • 154
  • 229

2 Answers2

2

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.

SilentGhost
  • 307,395
  • 66
  • 306
  • 293
Andrew
  • 26,629
  • 5
  • 63
  • 86
  • 1
    That wasn't in the original question but i've altered the string work you have to do. working out the basic string manipulation isn't tough work. – Andrew Oct 06 '09 at 11:19
1

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....

Stuart Ainsworth
  • 12,792
  • 41
  • 46