| Author |
 Topic  |
|
|
olibara
Yak Posting Veteran
84 Posts |
 Posted - 05/15/2011 : 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
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
5152 Posts |
Posted - 05/15/2011 : 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!! |
 |
|
|
olibara
Yak Posting Veteran
84 Posts |
Posted - 05/15/2011 : 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)
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 05/15/2011 : 08:11:12
|
| Try the STR() function, or cast to decimal, then varchar. |
|
|
|
Andrew Zwicker
Starting Member
USA
11 Posts |
Posted - 05/15/2011 : 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 |
Edited by - Andrew Zwicker on 05/15/2011 09:51:11 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/15/2011 : 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. |
Edited by - sunitabeck on 05/15/2011 11:02:57 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
| |
Topic |
|
CAST float to VARCHAR
