SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CAST float to VARCHAR
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

olibara
Yak Posting Veteran

89 Posts

Posted - 05/15/2011 :  02:59:49  Show Profile  Reply with Quote
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

SELECT CAST(MaDate AS VARCHAR);
Gives
quote:
2.01103e+007


How can I avoid that ?

Thanks for any help

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/15/2011 :  05:58:31  Show Profile  Reply with Quote
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

89 Posts

Posted - 05/15/2011 :  07:25:57  Show Profile  Reply with Quote
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

USA
15683 Posts

Posted - 05/15/2011 :  08:11:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
Try the STR() function, or cast to decimal, then varchar.
Go to Top of Page

Andrew Zwicker
Starting Member

USA
11 Posts

Posted - 05/15/2011 :  09:45:32  Show Profile  Reply with Quote
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

Edited by - Andrew Zwicker on 05/15/2011 09:51:11
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/15/2011 :  11:02:21  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 05/15/2011 11:02:57
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/15/2011 :  12:29:31  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000