CODEWITHSUNDEEP
sqlsql-server
user3038537
user3038537

Reputation: 9

Convert decimal hour.minutes to hh:mm tt format and viceverza

I need to convert a decimal value that is stored in hours.minutes (I believe value is stored in hours.hundreds) to a format like hh:mm: tt for example.

and viceverza

Upvotes: 0

Views: 396

Answers (5)

dnoeth
dnoeth

Reputation: 60462

Decimal to Time:

timefromparts(floor(x),(x % 1) * 60,0,0,0)

You should store the data using the TIME datatype, only for display you might cast it:

convert(varchar(7), timefromparts(floor(x),(x % 1) * 60,0,0,0), 100)

The TIME back to DECIMAL is simple:

cast(datepart(hour,t) + (datepart(minute,t)/60.) as dec(4,2))

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44316

This formular will give an exact match as described.

The strange constant .000147222 is for rounding up instead of truncating:

DECLARE @ table(col1 decimal(4,2), col2 time(0))
INSERT @
values
('11.00','11:00 AM'),('11.02','11:01 AM'),('11.03','11:02 AM'),('11.05','11:03 AM'),
('11.07','11:04 AM'),('11.08','11:05 AM'),('11.10','11:06 AM'),('11.12','11:07 AM'),
('11.13','11:08 AM'),('11.15','11:09 AM'),('11.17','11:10 AM'),('11.18','11:11 AM'),
('11.20','11:12 AM'),('11.22','11:13 AM'),('11.23','11:14 AM'),('11.25','11:15 AM'),
('11.27','11:16 AM'),('11.28','11:17 AM'),('11.30','11:18 AM'),('11.32','11:19 AM'),
('11.33','11:20 AM'),('11.35','11:21 AM'),('11.37','11:22 AM'),('11.38','11:23 AM'),
('11.40','11:24 AM'),('11.42','11:25 AM'),('11.50','11:30 AM'),('11.58','11:35 AM'),
('11.67','11:40 AM'),('11.75','11:45 AM'),('11.83','11:50 AM'),('11.92','11:55 AM'),
('23.00','11:00 PM'),('23.02','11:01 PM'),('23.03','11:02 PM'),
('23.05','11:03 PM'),('23.07','11:04 PM'),('23.08','11:05 PM')



SELECT 
  stuff(stuff(convert(varchar(19),
    cast((col1)/24+.000147222 as datetime),0), 1,12,''), 6,0,' ') Calculatedcol2, 
  col2,
  cast(cast(cast(col2 as datetime) as float)*24 as decimal(4,2)) CalculatedCol1,
  col1
FROM @

Upvotes: 1

Dmitry Kolchev
Dmitry Kolchev

Reputation: 2216

Try these

select 
    format(dateadd(minute, cast(((value % 1) * 60 + 0.5) as int), dateadd(hour,  cast(value as int), '1900-01-01')), 'hh:mm tt')
from
    (values
        (11.00), (11.02), (11.03), (11.05), (11.07), (11.08), (11.10), (11.12), (11.13), (11.15),
        (11.17), (11.18), (11.20), (11.22), (11.23), (11.25), (11.27), (11.28), (11.30), (11.32),
        (11.33), (11.35), (11.37), (11.38), (11.40), (11.42), (11.50), (11.58), (11.67), (11.75),
        (11.83), (11.92), (23.00), (23.02), (23.03), (23.05), (23.07), (23.08)) as T(value);

select
    cast(cast(cast(cast(time as datetime) as float) * 24000 as int) / 1000.0 as decimal(4,2))
from
    (values
        ('11:00 AM'), ('11:01 AM'), ('11:02 AM'), ('11:03 AM'), ('11:04 AM'), ('11:05 AM'), ('11:06 AM'), ('11:07 AM'), ('11:08 AM'),
        ('11:09 AM'), ('11:10 AM'), ('11:11 AM'), ('11:12 AM'), ('11:13 AM'), ('11:14 AM'), ('11:15 AM'), ('11:16 AM'), ('11:17 AM'),
        ('11:18 AM'), ('11:19 AM'), ('11:20 AM'), ('11:21 AM'), ('11:22 AM'), ('11:23 AM'), ('11:24 AM'), ('11:25 AM'), ('11:30 AM'),
        ('11:35 AM'), ('11:40 AM'), ('11:45 AM'), ('11:50 AM'), ('11:55 AM'), ('11:00 PM'), ('11:01 PM'), ('11:02 PM'), ('11:03 PM'),
        ('11:04 PM'), ('11:05 PM')) as T(time)

Upvotes: 0

JohnS
JohnS

Reputation: 2052

A little ugly but works in my SQL Server:

DECLARE @decimal_time FLOAT 
SET @decimal_time = 23.08
SELECT SUBSTRING(CONVERT(VARCHAR(100), CAST( @decimal_time / 24.0 AS DATETIME), 100), 13, 10)

Result->11:04PM

DECLARE @DecTimeStr varchar(8)
SET @DecTimeStr = '11:01 PM'
SELECT ROUND(CAST(CONVERT(DATETIME, @DecTimeStr, 120) AS FLOAT) * 24, 2)

Result->23.02

Upvotes: 0

Felipe Vidal
Felipe Vidal

Reputation: 505

Maybe...

a) From hundreds to clock time (tested):

SELECT TO_CHAR(TO_DATE('11.00', 'HH24.MI'), 'HH:MI AM') FROM DUAL;

b) From clock time to hundreds should be:

SELECT TO_CHAR(TO_DATE('11:00 PM', 'HH:MI AM'), 'HH.MI') FROM DUAL;

Upvotes: -1

Related Questions