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.
Author |
Topic |
olibara
Yak Posting Veteran
94 Posts |
Posted - 2011-05-15 : 02:59:49
|
HelloI have two tables with date representationUnfortunately those tables were imported from Excell and in one the date is in varchar, in the second the date is in floatHere is a float date examplequote: 20110309
But if I try to cast that date to varchar the resuts is in scientific notation[CODE]SELECT CAST(MaDate AS VARCHAR);[/CODE]Givesquote: 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!! |
|
|
olibara
Yak Posting Veteran
94 Posts |
Posted - 2011-05-15 : 07:25:57
|
I've found TWO wayOne :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
15732 Posts |
Posted - 2011-05-15 : 08:11:12
|
Try the STR() function, or cast to decimal, then varchar. |
|
|
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.000If the original values are floats instead of varchars, then str(...) can be used. Here's an example:declare @val floatset @val = 20110309select CONVERT(datetime, str(@val))Visit http://www.helpwithsql.com |
|
|
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. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|