sql - How to display a time in specific format -


using sql server 2005

table1

id timecolumn  001 13.00 002 03.30 003 14.00 004 23.00 005 08.30 ... 


table1 format

timecolumn format: hh:mm timecolumn datatype nvarchar timecolumn display time 1 hour or halfhour timecolumn not display 08.20, 08.56. display time 08.00, 08.30. 

i want display time 13 instead of 13.00, 3.5 instead of 03.30.

expected output

id   timecolumn  value  001  13.00       13 002  03.30       3.5 003  14.00       14 004  23.00       23 005  18.30       18.5 ... 

how make query above condition?

based on facts, there 2 cases last 3 digits, either

  • .30; or
  • .00

so replace them

select     id,     timecolumn,     value = replace(replace(timecolumn, '.30', '.5'), '.00', '') table1 

edit

to drop leading 0, can use instead (the value column numeric)

select     id,     timecolumn,     value = round(convert(float,timecolumn)*2,0)/2 table1 

or if need varchar

select     id,     timecolumn,     value = right(round(convert(float,timecolumn)*2,0)/2,5) table1 

Comments

Popular posts from this blog

apache - Add omitted ? to URLs -

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

php - How can I stop spam on my custom forum/blog? -