converting number to time

[giovi2002]

Is it possible to convert a number to a time format?
for instance 3,00 to 03:00
and 2,25 to 2:15

 

[vongrunt]

Yup:

select convert(varchar(5), convert(datetime, yourvaluehere/24.0), 108)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

yes here we go

If you can supply the values with dots instead of commas then you don't need to do the conversion

No conversion

DECLARE @a decimal(6,2) 
SELECT @a = 1.75
SELECT CONVERT(char(5), DATEADD(second, @a * 3600, 0), 108)

Take out commas and replace with dots and then convert to decimal

DECLARE @a decimal(6,2) 
DECLARE @v varchar(50)
SELECT @v= '2,25'   --'3,.0'   '1,.75'
SELECT @a = convert(decimal(6,2),replace(@v,',','.'))
SELECT CONVERT(char(5), DATEADD(second, @a * 3600, 0), 108)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

vongrunt,
I was comparing your code and my code and for some reason or another your code is off by a minute for these 2 values (2.5 and 2.75)

DECLARE @a decimal(6,2) 
SELECT @a = 2.25
SELECT CONVERT(char(5), DATEADD(second, @a * 3600, 0), 108) ,
 convert(varchar(5), convert(datetime, @a/24.0), 108) 


SELECT @a = 2.50
SELECT CONVERT(char(5), DATEADD(second, @a * 3600, 0), 108) ,
 convert(varchar(5), convert(datetime, @a/24.0), 108) 


SELECT @a = 2.75
SELECT CONVERT(char(5), DATEADD(second, @a * 3600, 0), 108) ,
 convert(varchar(5), convert(datetime, @a/24.0), 108) 


SELECT @a = 3.00
SELECT CONVERT(char(5), DATEADD(second, @a * 3600, 0), 108) ,
 convert(varchar(5), convert(datetime, @a/24.0), 108)

I am not nitpicking here, just wondering why that would be?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

I was wondering will anyone notice that problem...

1) constant 24.0 is implicitely considered decimal(3, 1). Divide decimal(6.2) with decimal(3.1) and result is... decimal(11, 6). Six decimals.

2) Result of division stored in finite-precision data type is not always accurate.. The most trivial example is 1/3 = 1.3333333333333333....

3) conversion from decimal to datetime makes that error margin visible (1 day = 86.400,000 milliseconds, 3ms precision). The same problem wouldn't happen with smalldatetime (accurate down to 1 minute = 1/1440 of day).

In other words:

SELECT @a = 2.75
SELECT CONVERT(char(5), DATEADD(second, @a * 3600, 0), 108) ,
 convert(varchar(5), convert(datetime, @a/24.0), 108),
 [b]convert(datetime, @a/24.0)[/b]

On another note, decimal(6, 2) also has finite precision. So some otherwise nicely rounded 60-base values have inaccurate presentation in 10-base. For example:

DECLARE @a decimal(6,2)
SELECT @a = 2.33 -- twenty minutes?
SELECT CONVERT(char(5), DATEADD(second, @a * 3600, 0), 108) ,
 convert(varchar(5), convert(smalldatetime, @a/24.0), 108)

Or even:

DECLARE @a decimal(35,34)
SELECT @a = 2.33333333333333333333333333333333333
SELECT CONVERT(char(5), DATEADD(second, @a * 3600, 0), 108) ,
 convert(varchar(5), convert(smalldatetime, @a/24.0), 108)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[giovi2002]

Guys thanks for your response.

Indeed vongrunt there is a decimal problem to get time exactly