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

TimeColumn Format: HH:MM
TimeColumn Datatype is nvarchar
TimeColumn will display the time One Hour or HalfHour
TimeColumn will not display 08.20, 08.56. It will display the time like 08.00, 08.30.

I want to display a time like 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 to make a query for the above condition?

Based on your facts, there are only 2 cases for the last 3 digits, either

• .30; or
• .00

So we just replace them

SELECT
    ID,
    TimeColumn,
    Value = replace(replace(TimeColumn, '.30', '.5'), '.00', '')
From Table1

EDIT

To drop the leading 0, you can use this instead (the Value column is numeric)

SELECT
    ID,
    TimeColumn,
    Value = round(convert(float,TimeColumn)*2,0)/2
From Table1

Or if you need it to be varchar

SELECT
    ID,
    TimeColumn,
    Value = right(round(convert(float,TimeColumn)*2,0)/2,5)
From Table1

Try this

SELECT
  DATEPART(hour,TimeColumn) + 
  1 / DATEPART(minute,TimeColumn) * 60 
  AS Value
FROM Table1

This is where TimeColumn is DateTime. For Column Type NVarChar use a String function to split hours and minutes.

I believe that is how SQL Server stores datetime, you then format it with your flavor of programming language.

Here is how you can do it:

select SUBSTRING('20.30', 1, 2) + (case when SUBSTRING('20.30', 4, 2) = '30' then '.5' else '' end)

just replace '20.30' with your column name and add from clause

Declare @table table (ID nvarchar(10),timevalue nvarchar(10))

INSERT INTO @table values('001','13.00')
INSERT INTO @table values('002','03.30')
INSERT INTO @table values('003','14.00')
INSERT INTO @table values('004','23.00')
INSERT INTO @table values('005','08.30')

select (CASE WHEN (CHARINDEX('.3',timevalue)>0) then convert(varchar(2),timevalue,2) 
        else convert(varchar(2),timevalue,2) + '.5'
end)
from @table

With TestInputs As
    (
    Select '001' As Id, Cast('13.00' As nvarchar(10)) As TimeColumn
    Union All Select '002','03.30'
    Union All Select '003','14.00'
    Union All Select '004','23.00'
    Union All Select '005','08.30'
    Union All Select '006','08.26'
    Union All Select '007','08.46'
    Union All Select '008','08.56'
    )
    , HoursMinutes As
    (
    Select Id, TimeColumn
        , Cast( Substring(TimeColumn
                    , 1
                    , CharIndex('.', TimeColumn) - 1 ) As int ) As [Hours]
        , Cast( Substring(TimeColumn
                    , CharIndex('.', TimeColumn) + 1
                    , Len(TimeColumn) ) As int ) As [Minutes]
    From TestInputs
    )
Select Id, TimeColumn
    , [Hours] + Case 
                When [Minutes] < 30 Then 0.0
                When [Minutes] < 60 Then 0.5
                Else 1
                End As Value
From HoursMinutes