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
Post a Comment