October 25, 2010 at 1:19 pm
Hi,
I am trying to export data from Excel 2007 to SQL Server 2005.
excel data
Average, Standard Deviation, t_min_median, t_max_median
74.64% 35.72% 22:30:00 3:30:00
64.01% 42.84% 23:30:00 11:30:00
48.29% 44.00% 0:30:00 17:30:00
when import to sql table data is like below
0.746428571428571, 0.35722269960311, 1899-12-30 22:30:00.000, 1899-12-30 03:30:00.000
Please advice on datatypes? Is there any datatype for only time in SQL?
October 25, 2010 at 2:30 pm
There is a Time datatype in SQL 2008. You posted in the 2005 forum, so I'm assuming you don't have access to that.
Just use Datetime and don't worry about the date. That's a usual solution for that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 25, 2010 at 2:38 pm
Thank you for your reply.
for columns 'Average','Standard Deviation', I want to store the data like 74.64%, 35.72%.
Just wondering what type, precision and scale need to use?
Appreciate your help.
October 25, 2010 at 2:41 pm
Looks like Numeric(4,2) would do what you need, but keep in mind that you're likely to end up with rounding causing issues.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 25, 2010 at 2:46 pm
Agreed - Numeric(4,2) or FLOAT.
October 25, 2010 at 2:54 pm
Float is more likely to keep it in the format that Excel uses, but that also has some issues.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 25, 2010 at 2:57 pm
If your data were being imported into a staging table, then the datetime values, could be converted to time only as a VARCHAR and then inserted into the proper column in the final table.
DECLARE @ThisDate DATETIME
DECLARE @Result VARCHAR(20)
SET @ThisDate = '1899-12-30 22:30:00.000'
Select convert(varchar(30), @ThisDate , 114)
Result: "22:30:00:000"
Keep in mind GSquared's post at Today @ 4:30 PM, it depends on what you will be doing in other calculations, if any, and the result of the above is VARCHAR
October 25, 2010 at 3:03 pm
I tried to use both float and numeric(4,2), but failed get 74.64(eg:)
with float, I got this
0.746428571428571
with numeric, I got this
0.74
October 25, 2010 at 3:10 pm
laddu4700 (10/25/2010)
I tried to use both float and numeric(4,2), but failed get 74.64(eg:)with float, I got this
0.746428571428571
with numeric, I got this
0.74
That's because the excel format is %, essentially divided by 100.
Multiply your column by 100 either on the input or manually after.
Preferably on the input so you don't lose data if you go with Numeric. Also, multiply by 100.00 due to how SQL handles significant digits.
October 26, 2010 at 6:32 am
If you really want to keep your % sign, then you'll have to import it as strings.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 26, 2010 at 7:00 am
da-zero (10/26/2010)
If you really want to keep your % sign, then you'll have to import it as strings.
Bad idea!
First, you won't be able to query it mathematically. Second, formatting and appearance should be handled by the presentation layer, not in the database, for a myriad of reasons.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2010 at 7:01 am
laddu4700 (10/25/2010)
I tried to use both float and numeric(4,2), but failed get 74.64(eg:)with float, I got this
0.746428571428571
with numeric, I got this
0.74
Expand the numeric to 6,4. Have the front end format the number. Or just use Float and have the front end format the number. If you've got all those digits in your source data, you never know when someone is going to ask you to start showing three or more digits after the decimal place.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2010 at 9:50 am
GSquared (10/26/2010)
da-zero (10/26/2010)
If you really want to keep your % sign, then you'll have to import it as strings.Bad idea!
First, you won't be able to query it mathematically. Second, formatting and appearance should be handled by the presentation layer, not in the database, for a myriad of reasons.
... that's why I said "If you really want to keep your % sign..."
We have no idea what requirements the original poster has. (and unfortunately we don't live in a world yet where the business/bosses only give sane requirements)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 26, 2010 at 11:47 am
da-zero (10/26/2010)
GSquared (10/26/2010)
da-zero (10/26/2010)
If you really want to keep your % sign, then you'll have to import it as strings.Bad idea!
First, you won't be able to query it mathematically. Second, formatting and appearance should be handled by the presentation layer, not in the database, for a myriad of reasons.
... that's why I said "If you really want to keep your % sign..."
We have no idea what requirements the original poster has. (and unfortunately we don't live in a world yet where the business/bosses only give sane requirements)
Too true.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply