Real to datetime - how to...?

[machina]

Hi,

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?

Thanks for help
Rgds
Mario

[Erland Sommarskog]

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx