how to convert float to timestamp in single select query (SQL)

Question

1.00/5 (1 vote)

See more:

Dear Sir/Madam

how to convert float to timestamp in single select query

for exp. i have float as 1.251152515236 ,

i want to convert this to datetime and from datetime to timestamp... i.e. 02:22:044456

I need sql query

It is actually .crd file made in xml , and i have query inside xml like

XML

<![CDATA[
SELECT USER.LastName, USER.fltime from Tbalname


]>

fltime has float datatype value which i need to convert to time

Regards
Maulik Shah

Posted 27-Mar-15 1:04am

maulikshah1990

Updated 27-Mar-15 1:13am

CHill60

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Answer 1 · 2015-03-27T01:18:00

Solution 1

Simple example:

SQL

declare @timeMoment float
set @timeMoment = 1.251152515236
select @timeMoment as floatValue, cast(@timeMoment as DateTime) as datetimeValue

Result:

floatValue	    datetimeValue
1.251152515236	1900-01-02 06:01:39.577

More smooth:

SQL

declare @timeMoment float
declare @datetimeValue datetime
set @timeMoment = 1.251152515236
set @datetimeValue = cast(@timeMoment as DateTime)

select  Right('00' + Cast(DATEPART(hh, @datetimeValue) as varchar(2)), 2) + ':'+
        Right('00' + Cast(DatePart(mi, @datetimeValue) as varchar(2)), 2) + ':'+
        Right('00' + Cast(DatePart(ss, @datetimeValue) as varchar(2)), 2) + '.'+
        Cast(DatePart(ms, @datetimeValue) as varchar)

in SQL Server the long SELECT Right..... ca also be changed to:

SQL

select cast(@datetimevalue as time) test

Posted 27-Mar-15 1:18am

Herman<T>.Instance

Updated 27-Mar-15 1:56am

v3

Comments

maulikshah1990 27-Mar-15 7:22am

i need only 06:01:36.577 from value ..

also how to display this because datatype is float ?

Herman<T>.Instance 27-Mar-15 7:54am

Improved solution

Herman<T>.Instance 27-Mar-15 7:57am

to display? the SQL query give the result in varchar result. You don't need to display the float. That's entry for the query

maulikshah1990 27-Mar-15 8:03am

i tried above cast and i get error as
Database error: [206] [Operand type clash: float is incompatible with time]

i used query as

SELECT tablname.LastName, cast(cast(SUM(tablname.CONVERSATION_TIME) AS datetime) as time) test,
from tablename;

CONVERSATION_TIME is float datatype

Herman<T>.Instance 27-Mar-15 8:34am

What is the Database you are using? Oracle, SAp/Sybase, MS SQL, ... ?

In MS SQL 2012 the next line works for me:
select cast(cast(@timeMoment as DateTime) as time) testfloat

maulikshah1990 27-Mar-15 8:51am

it is sql server

Herman<T>.Instance 27-Mar-15 9:02am

version?

maulikshah1990 27-Mar-15 9:06am

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.0 <x86> (Build 6000: )

Herman<T>.Instance 27-Mar-15 9:25am

ahhhhhh...
you do a SUM but not a group by!

Other tip:
declare @timeMoment float

select @timeMoment = SUM(tablname.CONVERSATION_TIME)
from tablename;
SELECT tablname.LastName, cast(cast(@timeMoment) AS datetime) as time) test,
from tablename;

maulikshah1990 30-Mar-15 6:11am

select @ does not work..i get error
Database error: [156] [Incorrect syntax near the keyword 'SELECT'.]

Herman<T>.Instance 30-Mar-15 6:13am

sorry! You are right Changed the query:
select @timeMoment = SUM(tablname.CONVERSATION_TIME)
from tablename;
SELECT tablname.LastName, cast(cast(@timeMoment) AS datetime) as time) test,
from tablename;

maulikshah1990 30-Mar-15 7:13am

it is same query,,

Herman<T>.Instance 30-Mar-15 7:31am

I changed it on both answers to the same. So when copying you would not get the wrong one accidently

maulikshah1990 30-Mar-15 8:37am

i tried

DECLARE @HOURS decimal(7,4) = 1.09796836411745
SELECT CC80_USER.LastName,RIGHT('0' + CAST (FLOOR(@HOURS) AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(FLOOR((((@HOURS * 3600) % 3600) / 60)) AS VARCHAR), 2) + ':' + RIGHT('0' + CAST (FLOOR((@HOURS * 3600) % 60) AS VARCHAR), 2),

and get error as
Database error: [8114] [Error converting data type varchar to float.]

Herman<T>.Instance 30-Mar-15 8:58am

Given your code I cannot understand the exception
- You declare a decimal, not a float
- Where do you convert a varchar to a float?

Are you sure the problem is in this area?

maulikshah1990 31-Mar-15 5:33am

if datafield is float with value 1.09796836411745 ,

i want to make query as

select name,floattotimefield from tablname where

floattotimefield value should be 02:22:14630.

all above for example.

did u understand ?

maulikshah1990 31-Mar-15 8:47am

waiting.......

Herman<T>.Instance 31-Mar-15 8:49am

.... thinking. I understand what you want. But the errormessage varchar to float is strange, since in the code no float is used but decimal. Have you checked with SQL Express Profiler how the query looks like when sending to the db?

maulikshah1990 31-Mar-15 9:09am

it is actually a report file in xml where we have this query inside tags

<generatesqlstatement>
<sqlstatement>
<![CDATA[

select ....

]]>

this report has control and we only change query in xml and upload on server..

and when i run report file, it says error as described ..

Herman<T>.Instance 31-Mar-15 9:43am

A bell starts to ring! You send an XML file and all the text is treated as varchar. Then you need that varchar part 1.097... as float. And then you get the error.

Try this:
declare @timevarchar as varchar
declare @timeMoment float
set @timevarchar='1.251152515236'
set @timeMoment = cast(@timevarchar as float)
select @timevarchar as floatChar, @timeMoment as floatValue, cast(@timeMoment as DateTime) as datetimeValue

And now how to fix? That's a good question!

maulikshah1990 1-Apr-15 1:17am

Hi,

value 1.251152515236 is float value in database,,, and i need query for select float value into time (hh:mm:ssss) , from table

Herman<T>.Instance 1-Apr-15 11:20am

Just works in SSMS:
declare @timeMoment float
set @timeMoment = 1.251152515236
select @timeMoment as floatValue, cast(cast(@timeMoment as DateTime) as time) as datetimeValue

test it yourself

maulikshah1990 2-Apr-15 1:19am

i tried the above in xml file (report file) , and

Database error: [206] [Operand type clash: float is incompatible with time]

Herman<T>.Instance 2-Apr-15 3:56am

But does it work for you in SSMS?

maulikshah1990 2-Apr-15 4:12am

hi,
currently i dont have access to sql database directly .. also if i try from ssms , i need to have solution for xml .

Herman<T>.Instance 2-Apr-15 4:29am

It is just to dedicate the problem is in XML and not in DB

maulikshah1990 2-Apr-15 4:45am

but can u please explain why i get error Error converting data type varchar to float .. what the error exactly means..??

maulikshah1990 6-Apr-15 5:00am

but can u please explain why i get error Error converting data type varchar to float .. what the error exactly means..??