Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CAST float to VARCHAR

Author  Topic 

olibara
Yak Posting Veteran

94 Posts

Posted - 2011-05-15 : 02:59:49
Hello

I have two tables with date representation

Unfortunately those tables were imported from Excell and in one the date is in varchar, in the second the date is in float

Here is a float date example
quote:
20110309


But if I try to cast that date to varchar the resuts is in scientific notation

[CODE]SELECT CAST(MaDate AS VARCHAR);[/CODE]Gives
quote:
2.01103e+007


How can I avoid that ?

Thanks for any help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-15 : 05:58:31
Unfortunately, I don't know any way you can recover the data in this scenario!! By default, Excel assigns numeric format to anything that looks like a number. Then, if the number is large, as can be in the case of dates appended with time represented as a number, it uses scientific format.

The only thing I have been able to do is to reimport a new file which has the correct data - i.e., the numbers formatted as text, so they do not turn into scientific notation monsters!!
Go to Top of Page

olibara
Yak Posting Veteran

94 Posts

Posted - 2011-05-15 : 07:25:57
I've found TWO way

One :

LTRIM(str(cast(DATEST as float)));

Two (better) Suggessted by SqlPro in another forum :

CAST(CAST(CAST(@D AS INT) AS CHAR(8)) AS DATETIME)
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-15 : 08:11:12
Try the STR() function, or cast to decimal, then varchar.
Go to Top of Page

Andrew Zwicker
Starting Member

11 Posts

Posted - 2011-05-15 : 09:45:32
Hi,

Another option is to use convert:

select CONVERT(datetime, '20110309')

This returns the datetime as: 2011-03-09 00:00:00.000

If the original values are floats instead of varchars, then str(...) can be used. Here's an example:

declare @val float
set @val = 20110309

select CONVERT(datetime, str(@val))

Visit http://www.helpwithsql.com
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-15 : 11:02:21
This is nice to know! In my case, the data was character data in a csv file, which had already lost the information beyond the sixth digit. In retrospect, I might have been able to make my life simpler if I had just requested the data in the original Excel format instead of the csv format, even if it was in scientific notation.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-15 : 12:29:31
excel bytes

Can you save it first to a delimited file?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page
   

- Advertisement -