CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2005t-sql
Gopal
Gopal

Reputation: 11972

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

Upvotes: 0

Views: 238

Answers (6)

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

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

Upvotes: 2

Thomas
Thomas

Reputation: 64635

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

Upvotes: 0

ashish.chotalia
ashish.chotalia

Reputation: 3746

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

Upvotes: 0

Danil
Danil

Reputation: 1893

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

Upvotes: 0

bw_&#252;ezi
bw_&#252;ezi

Reputation: 4564

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.

Upvotes: 1

The Muffin Man
The Muffin Man

Reputation: 20004

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

Upvotes: 0

Related Questions