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
 format changes when transferring the rows

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.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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)
Go to Top of Page

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?

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-14 : 07:37:58
1 Always use proper DATETIME datatype to store dates
2 If dates come from another system with different format, change it to DATETIME datatype while adding it to the table
ex

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_date


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -