Posted by Erland Sommarskog on 03/05/07 22:11

machina (mm(at)home.pl) writes:
> I would like to convert real data type to datetime type. Example:
> I have a real data type which is: 23,613456 (23 hours and 0,613456). I
> would like to have it in hh:mm:ss format. How to do this? Can I use
> convert/cas function?

There is a slight confusion here. datetime is a binary format. hh:mm:ss
is a string. But this is possible, although accuracy with the real data
type os poor. This how you do it:

declare @d real
select @d = 23.613456
select @d = @d /24
select convert(char(8), convert(datetime, @d), 108)

The division with 24 is necessary, because a datetime value consists
of two parts, which could be interpreted as a decimal number, with
the integer parts being number of days since 1900-01-01 and the
fractional hours being something since midnight.

Of course, I have no idea whether your interpretation of 23,613456
agrees with what the above gives you.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]