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 |
anjali5
Posting Yak Master
121 Posts |
Posted - 2012-05-10 : 14:03:52
|
Hi All, I have this date value in one my column 201012101500, this is basically 2010 year, 12 month and 10 date 15 minutes and 10 seconds. when I insert this value in another table, it becomes 2.01012e+011. I am not sure why it does that, but it converts that nvarchar value to 2.01012e+011.both the column types are nvarchar.How can I keep the format the way it is.Thanks. |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2012-05-10 : 14:33:28
|
1) Show us your insert statement, and also the destination table's definition to confirm that there's no converting taking place.2) Where exactly are you seeing that the value is 2.01012e+011 ? Front-ends like Excel are notorious for ignoring (or misinterpreting) data types and displaying things as it sees fit. Just because something is displayed by a client one way does not mean it is stored that way in the database table.3) Why are you not using DateTime data types to store your dates? All of these issues are avoided, and many more advantages are gained, by simply using proper data types.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
anjali5
Posting Yak Master
121 Posts |
Posted - 2012-05-10 : 16:30:11
|
Hi,Sorry, my qustion was wrong.I ran the DTS package in sql server 2005 to insert data from Excel spreadsheet to sql server. There is a column in Excel spreadsheet that has DateTime group stored in this way 200710091115. when I run the DTS package then the new table that is created has the values for that field with a e+ in it. If I declare that particular datetime group column as float then it works fine, but if I declare that column as varchar(255) then I can see e+ in it.Is their any way I can avoid changing the value to e+, my column type is nvarchar.Please let me know if you need any additional info.Thanks. |
|
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-05-10 : 17:07:11
|
What datatype has you ddefined for column in which your are populating the this data? |
|
|
anjali5
Posting Yak Master
121 Posts |
Posted - 2012-05-10 : 17:35:21
|
quote: Originally posted by vijays3 What datatype has you ddefined for column in which your are populating the this data?
nvarchar(12) |
|
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-05-10 : 17:42:00
|
In excel file change the cell format for that column ,make it custom and try to load the data into your table . is your date data in your excel is stored as 201012101500? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-05-14 : 07:37:58
|
1 Always use proper DATETIME datatype to store dates2 If dates come from another system with different format, change it to DATETIME datatype while adding it to the tableex declare @n nvarchar(12) set @n='201012101500' select cast(stuff(stuff(stuff(stuff(@n,11,0,':'),9,0,':'),7,0,':'),5,0,' ') as datetime) as proper_dateMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|