December 8, 2015 at 9:16 am
Excel
format display
:mm:ss 100:23:24
yyyy-mm-dd hh:mm 1900-01-04 04:23
uu:mm:ss 04:23:24
Above the formats are different.
The datatype is the same and the value is the same.
The values are imported in SQL-server, the format is of no consequence, but the value is.
When converting to a varchar and the value is more than 1, the date presentation is included.
When converting to a varchar and the value is less than 1, only the time presentation is made.
So avoid the varchar datatype in SQL-server.
Use the float datatype instead. Reason then you get the number of days past since (see above).
The fraction part then is that part of a day. (1/3 is then 08:00).
--
-- examples of a calculated column using the time of a varchar or a datetime field.
--
CREATE TABLE dbo.import_time
(
ID int IDENTITY (1,1) NOT NULL
, float_in float
, time_in varchar(30)
, time_in2 datetime
-- , time_string varchar(30) -- convert(varchar(30), time_in2, 126)
, time_field1 as cast(time_in as time(0))
, time_field2 as cast(time_in2 as time(0))
, time_string3 varchar(5)
, time_string4 as convert(varchar(30), time_in2,126)
, time_string5 as substring(convert(varchar(30), time_in2,126),12,5)
, time_string6 as substring(convert(varchar(30), time_in2,126),12,12)
, time_string7 as substring(convert(varchar(30), convert(datetime,float_in),126),12,12)
, time_hours as convert(int,float_in * 24)
, time_minutes as convert(int,float_in * 24*60)%60
, time_seconds as convert(bigint,float_in * 24*3600)%60
);
-- Insert values into the table.
INSERT INTO dbo.import_time (time_in, time_in2)
VALUES ('11:30:43','11:30:40')
,('20151205 12:30','20151205 12:30')
,('20131205 13:30','20131205 13:30')
,('19000101 14:30','19000101 14:30')
,('19000103 17:30:33','19000103 17:30:33');
update import_time set time_string3 = substring(convert(varchar(30), time_in2, 126),12,5)
update import_time set float_in = CONVERT(float,time_in2)
-- Display the rows in the table.
SELECT * FROM dbo.import_time;
DROP TABLE dbo.import_time
Be aware when working with times in Excel an SQL-server, that the internal storage is different.
See for example 14:29:59.997 in time_string 7 for the input time of 14:30:00.
In excel the time 08:00 is represented as 1/3 with a limited resolution.
In SQL-server the time 08:00 is stored exactly as 08:00:00.000 (10/3 microsecond resolution).
(The steps in the SQL-server timing are not equal in size).
Basics of the code is supplied. Use the parts of this to you own liking.
ben
December 8, 2015 at 9:34 am
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
ben.brugman (12/8/2015)
pwalter83 (12/8/2015)
Thanks for your suggestion. However, it still doesn't help with my basic requirement. I have to convert the time duration column in excel specifically where values like 100:20:30 exist.
What is the datatype in Excel?
What is the datatype in SQL-server?
Show some examples of the content in Excel and SQL-server.
Thanks for your reply. I need to import exact data from excel to sql.
The datatype for the time duration column in excel is *hh:mm:ss - Examples- 100:20:55 and 21:10:27
I have tried the datatype nvarchar in SQL so what it does is if for example the value in excel is 21:10:27 it copies it exactly across to SQL as 21:10:27. However, if the value is 100:20:55 it displays the value in SQL as 1900-01-01 04:20:55 because for some reason its not displaying the hours greater than 24.
I also tried to change the datatype to datetime in SQL and then added a computed column with the following formula:
(CONVERT([varchar](10),datediff(hour,(0),[Time Elapsed]),(0))+right(CONVERT([char](8),[Time Elapsed],(108)),(6)))
But it also doesn't help as I get incorrect values in the computed column. Thanks.
I think you are missing the point here. You seem to be confusing display text with datatypes. When you type 100:20:55 in Excel it will automatically decided this a date (1/4/1900 4:20:55 AM). This is clearly not what you want because it is NOT text that you typed in. You need to force this to be text instead of the assumed datatype that Excel will utilize. This is not an issue on the sql server side, it is in the import where you need to change this. The bigger issue is that we don't how you are importing this. Are you using the data import wizard in SSMS? An SSIS package?
Just to clarify:
1. I am not typing the values in EXCEL, the time duration and other values in EXCEL are automatically extracted through a different system. I cannot change the data type in EXCEL as when attempted, the time duration values change to some other format which I do not want.
2. I mentioned above as well, I am using the IMPORT DATA wizard in SQL to import data from EXCEL to SQL.
3. I just need the time duration values in EXCEL to be exactly imported across as is to SQL, that's it, even if the value is 100:20:15.
Thanks.
December 8, 2015 at 9:40 am
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
ben.brugman (12/8/2015)
pwalter83 (12/8/2015)
Thanks for your suggestion. However, it still doesn't help with my basic requirement. I have to convert the time duration column in excel specifically where values like 100:20:30 exist.
What is the datatype in Excel?
What is the datatype in SQL-server?
Show some examples of the content in Excel and SQL-server.
Thanks for your reply. I need to import exact data from excel to sql.
The datatype for the time duration column in excel is *hh:mm:ss - Examples- 100:20:55 and 21:10:27
I have tried the datatype nvarchar in SQL so what it does is if for example the value in excel is 21:10:27 it copies it exactly across to SQL as 21:10:27. However, if the value is 100:20:55 it displays the value in SQL as 1900-01-01 04:20:55 because for some reason its not displaying the hours greater than 24.
I also tried to change the datatype to datetime in SQL and then added a computed column with the following formula:
(CONVERT([varchar](10),datediff(hour,(0),[Time Elapsed]),(0))+right(CONVERT([char](8),[Time Elapsed],(108)),(6)))
But it also doesn't help as I get incorrect values in the computed column. Thanks.
I think you are missing the point here. You seem to be confusing display text with datatypes. When you type 100:20:55 in Excel it will automatically decided this a date (1/4/1900 4:20:55 AM). This is clearly not what you want because it is NOT text that you typed in. You need to force this to be text instead of the assumed datatype that Excel will utilize. This is not an issue on the sql server side, it is in the import where you need to change this. The bigger issue is that we don't how you are importing this. Are you using the data import wizard in SSMS? An SSIS package?
Just to clarify:
1. I am not typing the values in EXCEL, the time duration and other values in EXCEL are automatically extracted through a different system. I cannot change the data type in EXCEL as when attempted, the time duration values change to some other format which I do not want.
2. I mentioned above as well, I am using the IMPORT DATA wizard in SQL to import data from EXCEL to SQL.
3. I just need the time duration values in EXCEL to be exactly imported across as is to SQL, that's it, even if the value is 100:20:15.
Thanks.
I didn't think you were typing this into Excel. I was trying to explain what happens in Excel.
What is the datatype of the column you are importing to?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 8, 2015 at 9:48 am
But you are still missing the point.
The datatype in Excel does NOT vary. (ONLY the format is different).
What is important is the datatype of the field in SQL-server. (Again not the format).
See below with the three starting points FLOAT,VARCHAR,DATETIME.
Hours, minutes and seconds are given as numbers. If it is single digit it is shown as single digit.
This is left to the reader to improve.
Also when a different format of the date is used, this is left to the reader to change the code accordingly.
(I like to use the yyyymmdd format, because this is less confusing, for me and SQL-server).
Ben
--
-- examples of a calculated column using the time of a varchar or a datetime field.
--
CREATE TABLE dbo.import_time
(
ID int IDENTITY (1,1) NOT NULL
, float_in float
, time_in varchar(30)
, time_in2 datetime
-- Starting from a Float
, time_x0 as
convert(varchar(7),convert(int,float_in * 24))+':'+
convert(varchar(2),convert(int,float_in * 24*60)%60)+':'+
convert(varchar(2),convert(bigint,float_in * 24*3600)%60)
-- Sarting from a Varchar
, time_x1 as
convert(varchar(7),convert(int,convert(float,convert(datetime,time_in)) * 24))+':'+
convert(varchar(2),convert(int,convert(float,convert(datetime,time_in)) * 24*60)%60)+':'+
convert(varchar(2),convert(bigint,convert(float,convert(datetime,time_in)) * 24*3600)%60)
-- Starting from a datetime format
, time_x2 as
convert(varchar(7),convert(int,convert(float,time_in2) * 24))+':'+
convert(varchar(2),convert(int,convert(float,time_in2) * 24*60)%60)+':'+
convert(varchar(2),convert(bigint,convert(float,time_in2) * 24*3600)%60)
);
-- Insert values into the table.
INSERT INTO dbo.import_time (time_in, time_in2)
VALUES ('11:30:43','11:30:40')
,('20151205 12:30','20151205 12:30')
,('20131205 13:30','20131205 13:30')
,('19000101 14:30','19000101 14:30')
,('19000103 17:30:33','19000103 17:30:33')
,('1900-02-03 21:39:35','1900-02-05 21:39:35')
,('1900-02-04 21:39:35','1900-02-05 21:39:35')
,('1900-02-05 21:39:35','1900-02-05 21:39:35')
,('1900-02-06 21:39:35','1900-02-05 21:39:35')
,('1900-02-07 21:39:35','1900-02-05 21:39:35')
-- ,('1900-04-29 21:39:35','1900-02-05 21:39:35') -- commented out, because this date might break a yyyy-dd-mm format system.
-- ,('1900-04-30 21:39:35','1900-02-05 21:39:35') -- commented out, because this date might break a yyyy-dd-mm format system.
,('1900-05-01 21:39:35','1900-02-05 21:39:35')
,('1900-05-02 21:39:35','1900-02-05 21:39:35')
,('1900-05-03 21:39:35','1900-02-05 21:39:35');
update import_time set float_in = CONVERT(float,time_in2)
-- Display the rows in the table.
SELECT * FROM dbo.import_time;
December 8, 2015 at 9:58 am
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
ben.brugman (12/8/2015)
pwalter83 (12/8/2015)
Thanks for your suggestion. However, it still doesn't help with my basic requirement. I have to convert the time duration column in excel specifically where values like 100:20:30 exist.
What is the datatype in Excel?
What is the datatype in SQL-server?
Show some examples of the content in Excel and SQL-server.
Thanks for your reply. I need to import exact data from excel to sql.
The datatype for the time duration column in excel is *hh:mm:ss - Examples- 100:20:55 and 21:10:27
I have tried the datatype nvarchar in SQL so what it does is if for example the value in excel is 21:10:27 it copies it exactly across to SQL as 21:10:27. However, if the value is 100:20:55 it displays the value in SQL as 1900-01-01 04:20:55 because for some reason its not displaying the hours greater than 24.
I also tried to change the datatype to datetime in SQL and then added a computed column with the following formula:
(CONVERT([varchar](10),datediff(hour,(0),[Time Elapsed]),(0))+right(CONVERT([char](8),[Time Elapsed],(108)),(6)))
But it also doesn't help as I get incorrect values in the computed column. Thanks.
I think you are missing the point here. You seem to be confusing display text with datatypes. When you type 100:20:55 in Excel it will automatically decided this a date (1/4/1900 4:20:55 AM). This is clearly not what you want because it is NOT text that you typed in. You need to force this to be text instead of the assumed datatype that Excel will utilize. This is not an issue on the sql server side, it is in the import where you need to change this. The bigger issue is that we don't how you are importing this. Are you using the data import wizard in SSMS? An SSIS package?
Just to clarify:
1. I am not typing the values in EXCEL, the time duration and other values in EXCEL are automatically extracted through a different system. I cannot change the data type in EXCEL as when attempted, the time duration values change to some other format which I do not want.
2. I mentioned above as well, I am using the IMPORT DATA wizard in SQL to import data from EXCEL to SQL.
3. I just need the time duration values in EXCEL to be exactly imported across as is to SQL, that's it, even if the value is 100:20:15.
Thanks.
I didn't think you were typing this into Excel. I was trying to explain what happens in Excel.
What is the datatype of the column you are importing to?
Thanks Sean, sorry I misunderstood you. When I first started this thread I hoped to get it resolved quickly but it got complex and frustrating.
I am importing the values from EXCEL to a new table in SQL so I assigned the nvarchar datatype to the associated time duration column in SQL. It works partially correct so when the values are less than 24 hours like 21:10:25, they are imported across as is to SQL as 21:10:25.
The problem starts when the hours value exceed 24 like 100:20:55 in EXCEL. So when this is imported to SQL, it is displayed as 1900-01-01 04:20:55.
Upon assigning the datetime datatype to SQL, the issue still is not resolved as now if the value in EXCEL is 885:39:35 it is displayed as '1900-02-05 21:39:35' in SQL.
I hope I have explained it properly. Thanks.
December 8, 2015 at 10:09 am
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
ben.brugman (12/8/2015)
pwalter83 (12/8/2015)
Thanks for your suggestion. However, it still doesn't help with my basic requirement. I have to convert the time duration column in excel specifically where values like 100:20:30 exist.
What is the datatype in Excel?
What is the datatype in SQL-server?
Show some examples of the content in Excel and SQL-server.
Thanks for your reply. I need to import exact data from excel to sql.
The datatype for the time duration column in excel is *hh:mm:ss - Examples- 100:20:55 and 21:10:27
I have tried the datatype nvarchar in SQL so what it does is if for example the value in excel is 21:10:27 it copies it exactly across to SQL as 21:10:27. However, if the value is 100:20:55 it displays the value in SQL as 1900-01-01 04:20:55 because for some reason its not displaying the hours greater than 24.
I also tried to change the datatype to datetime in SQL and then added a computed column with the following formula:
(CONVERT([varchar](10),datediff(hour,(0),[Time Elapsed]),(0))+right(CONVERT([char](8),[Time Elapsed],(108)),(6)))
But it also doesn't help as I get incorrect values in the computed column. Thanks.
I think you are missing the point here. You seem to be confusing display text with datatypes. When you type 100:20:55 in Excel it will automatically decided this a date (1/4/1900 4:20:55 AM). This is clearly not what you want because it is NOT text that you typed in. You need to force this to be text instead of the assumed datatype that Excel will utilize. This is not an issue on the sql server side, it is in the import where you need to change this. The bigger issue is that we don't how you are importing this. Are you using the data import wizard in SSMS? An SSIS package?
Just to clarify:
1. I am not typing the values in EXCEL, the time duration and other values in EXCEL are automatically extracted through a different system. I cannot change the data type in EXCEL as when attempted, the time duration values change to some other format which I do not want.
2. I mentioned above as well, I am using the IMPORT DATA wizard in SQL to import data from EXCEL to SQL.
3. I just need the time duration values in EXCEL to be exactly imported across as is to SQL, that's it, even if the value is 100:20:15.
Thanks.
I didn't think you were typing this into Excel. I was trying to explain what happens in Excel.
What is the datatype of the column you are importing to?
Thanks Sean, sorry I misunderstood you. When I first started this thread I hoped to get it resolved quickly but it got complex and frustrating.
I am importing the values from EXCEL to a new table in SQL so I assigned the nvarchar datatype to the associated time duration column in SQL. It works partially correct so when the values are less than 24 hours like 21:10:25, they are imported across as is to SQL as 21:10:25.
The problem starts when the hours value exceed 24 like 100:20:55 in EXCEL. So when this is imported to SQL, it is displayed as 1900-01-01 04:20:55.
Upon assigning the datetime datatype to SQL, the issue still is not resolved as now if the value in EXCEL is 885:39:35 it is displayed as '1900-02-05 21:39:35' in SQL.
I hope I have explained it properly. Thanks.
As someone else said, this is not a table creation problem, it's a data conversion problem. Fix the conversion.
Instead of using the import/export wizard, create a simple SSIS package. Which is actually what the import / export wizard creates.
What is happening is that SQL is making a decision on the nature of the data in the spreadsheet, and performing a conversion based upon what it thinks.
In the package, you need an Excel connection, a SQL native connection, and a data flow task. In the data flow task, you will need a Data Conversion task. The field from Excel should be converted to nvarchar.
That will likely solve your problem.
You may want to start with this tutorial
http://www.sqlservercentral.com/stairway/72494/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 8, 2015 at 10:12 am
pwalter83 (12/8/2015)
Upon assigning the datetime datatype to SQL, the issue still is not resolved as now if the value in EXCEL is 885:39:35 it is displayed as '1900-02-05 21:39:35' in SQL.
I hope I have explained it properly. Thanks.
I have added ,('1900-02-05 21:39:35','1900-02-05 21:39:35'); to the example.
So that you can check the results yourself.
In the last three columns it gets displayed as 861:39:35
ben
December 8, 2015 at 10:18 am
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
ben.brugman (12/8/2015)
pwalter83 (12/8/2015)
Thanks for your suggestion. However, it still doesn't help with my basic requirement. I have to convert the time duration column in excel specifically where values like 100:20:30 exist.
What is the datatype in Excel?
What is the datatype in SQL-server?
Show some examples of the content in Excel and SQL-server.
Thanks for your reply. I need to import exact data from excel to sql.
The datatype for the time duration column in excel is *hh:mm:ss - Examples- 100:20:55 and 21:10:27
I have tried the datatype nvarchar in SQL so what it does is if for example the value in excel is 21:10:27 it copies it exactly across to SQL as 21:10:27. However, if the value is 100:20:55 it displays the value in SQL as 1900-01-01 04:20:55 because for some reason its not displaying the hours greater than 24.
I also tried to change the datatype to datetime in SQL and then added a computed column with the following formula:
(CONVERT([varchar](10),datediff(hour,(0),[Time Elapsed]),(0))+right(CONVERT([char](8),[Time Elapsed],(108)),(6)))
But it also doesn't help as I get incorrect values in the computed column. Thanks.
I think you are missing the point here. You seem to be confusing display text with datatypes. When you type 100:20:55 in Excel it will automatically decided this a date (1/4/1900 4:20:55 AM). This is clearly not what you want because it is NOT text that you typed in. You need to force this to be text instead of the assumed datatype that Excel will utilize. This is not an issue on the sql server side, it is in the import where you need to change this. The bigger issue is that we don't how you are importing this. Are you using the data import wizard in SSMS? An SSIS package?
Just to clarify:
1. I am not typing the values in EXCEL, the time duration and other values in EXCEL are automatically extracted through a different system. I cannot change the data type in EXCEL as when attempted, the time duration values change to some other format which I do not want.
2. I mentioned above as well, I am using the IMPORT DATA wizard in SQL to import data from EXCEL to SQL.
3. I just need the time duration values in EXCEL to be exactly imported across as is to SQL, that's it, even if the value is 100:20:15.
Thanks.
I didn't think you were typing this into Excel. I was trying to explain what happens in Excel.
What is the datatype of the column you are importing to?
Thanks Sean, sorry I misunderstood you. When I first started this thread I hoped to get it resolved quickly but it got complex and frustrating.
I am importing the values from EXCEL to a new table in SQL so I assigned the nvarchar datatype to the associated time duration column in SQL. It works partially correct so when the values are less than 24 hours like 21:10:25, they are imported across as is to SQL as 21:10:25.
The problem starts when the hours value exceed 24 like 100:20:55 in EXCEL. So when this is imported to SQL, it is displayed as 1900-01-01 04:20:55.
Upon assigning the datetime datatype to SQL, the issue still is not resolved as now if the value in EXCEL is 885:39:35 it is displayed as '1900-02-05 21:39:35' in SQL.
I hope I have explained it properly. Thanks.
I understand (and can sense) your frustration. This type of frustration can be avoided by providing details in your questions. The people here are trying to help but getting the details has been like pulling teeth. This is partly due to your frustration but also perhaps because you don't realize that some of this data seems irrelevant but is actually critical to helping you.
The problem you are facing is because the import wizard assumes the source datatype is datetime because that is what Excel has done. I don't think there is a way around this. You will likely need to either use an SSIS package so you can control the source data or do some datemath after importing to update the values to what you want them to be.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 8, 2015 at 10:37 am
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
ben.brugman (12/8/2015)
pwalter83 (12/8/2015)
Thanks for your suggestion. However, it still doesn't help with my basic requirement. I have to convert the time duration column in excel specifically where values like 100:20:30 exist.
What is the datatype in Excel?
What is the datatype in SQL-server?
Show some examples of the content in Excel and SQL-server.
Thanks for your reply. I need to import exact data from excel to sql.
The datatype for the time duration column in excel is *hh:mm:ss - Examples- 100:20:55 and 21:10:27
I have tried the datatype nvarchar in SQL so what it does is if for example the value in excel is 21:10:27 it copies it exactly across to SQL as 21:10:27. However, if the value is 100:20:55 it displays the value in SQL as 1900-01-01 04:20:55 because for some reason its not displaying the hours greater than 24.
I also tried to change the datatype to datetime in SQL and then added a computed column with the following formula:
(CONVERT([varchar](10),datediff(hour,(0),[Time Elapsed]),(0))+right(CONVERT([char](8),[Time Elapsed],(108)),(6)))
But it also doesn't help as I get incorrect values in the computed column. Thanks.
I think you are missing the point here. You seem to be confusing display text with datatypes. When you type 100:20:55 in Excel it will automatically decided this a date (1/4/1900 4:20:55 AM). This is clearly not what you want because it is NOT text that you typed in. You need to force this to be text instead of the assumed datatype that Excel will utilize. This is not an issue on the sql server side, it is in the import where you need to change this. The bigger issue is that we don't how you are importing this. Are you using the data import wizard in SSMS? An SSIS package?
Just to clarify:
1. I am not typing the values in EXCEL, the time duration and other values in EXCEL are automatically extracted through a different system. I cannot change the data type in EXCEL as when attempted, the time duration values change to some other format which I do not want.
2. I mentioned above as well, I am using the IMPORT DATA wizard in SQL to import data from EXCEL to SQL.
3. I just need the time duration values in EXCEL to be exactly imported across as is to SQL, that's it, even if the value is 100:20:15.
Thanks.
I didn't think you were typing this into Excel. I was trying to explain what happens in Excel.
What is the datatype of the column you are importing to?
Thanks Sean, sorry I misunderstood you. When I first started this thread I hoped to get it resolved quickly but it got complex and frustrating.
I am importing the values from EXCEL to a new table in SQL so I assigned the nvarchar datatype to the associated time duration column in SQL. It works partially correct so when the values are less than 24 hours like 21:10:25, they are imported across as is to SQL as 21:10:25.
The problem starts when the hours value exceed 24 like 100:20:55 in EXCEL. So when this is imported to SQL, it is displayed as 1900-01-01 04:20:55.
Upon assigning the datetime datatype to SQL, the issue still is not resolved as now if the value in EXCEL is 885:39:35 it is displayed as '1900-02-05 21:39:35' in SQL.
I hope I have explained it properly. Thanks.
I understand (and can sense) your frustration. This type of frustration can be avoided by providing details in your questions. The people here are trying to help but getting the details has been like pulling teeth. This is partly due to your frustration but also perhaps because you don't realize that some of this data seems irrelevant but is actually critical to helping you.
The problem you are facing is because the import wizard assumes the source datatype is datetime because that is what Excel has done. I don't think there is a way around this. You will likely need to either use an SSIS package so you can control the source data or do some datemath after importing to update the values to what you want them to be.
I have tried it through SSIS package as well now as you suggested but it still doesn't display the values exactly like in EXCEL.
I really don't understand what data I missed in my first post. ok no worries, I will try some other way or may be jump of the building. Thanks.
December 8, 2015 at 10:39 am
ben.brugman (12/8/2015)
pwalter83 (12/8/2015)
Upon assigning the datetime datatype to SQL, the issue still is not resolved as now if the value in EXCEL is 885:39:35 it is displayed as '1900-02-05 21:39:35' in SQL.
I hope I have explained it properly. Thanks.
I have added ,('1900-02-05 21:39:35','1900-02-05 21:39:35'); to the example.
So that you can check the results yourself.
In the last three columns it gets displayed as 861:39:35
ben
Thanks Ben, but I am getting the value as 2925:39:35. I don't know how you managed the value 861:39:35 but that is correct.
December 8, 2015 at 10:45 am
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
Sean Lange (12/8/2015)
pwalter83 (12/8/2015)
ben.brugman (12/8/2015)
pwalter83 (12/8/2015)
Thanks for your suggestion. However, it still doesn't help with my basic requirement. I have to convert the time duration column in excel specifically where values like 100:20:30 exist.
What is the datatype in Excel?
What is the datatype in SQL-server?
Show some examples of the content in Excel and SQL-server.
Thanks for your reply. I need to import exact data from excel to sql.
The datatype for the time duration column in excel is *hh:mm:ss - Examples- 100:20:55 and 21:10:27
I have tried the datatype nvarchar in SQL so what it does is if for example the value in excel is 21:10:27 it copies it exactly across to SQL as 21:10:27. However, if the value is 100:20:55 it displays the value in SQL as 1900-01-01 04:20:55 because for some reason its not displaying the hours greater than 24.
I also tried to change the datatype to datetime in SQL and then added a computed column with the following formula:
(CONVERT([varchar](10),datediff(hour,(0),[Time Elapsed]),(0))+right(CONVERT([char](8),[Time Elapsed],(108)),(6)))
But it also doesn't help as I get incorrect values in the computed column. Thanks.
I think you are missing the point here. You seem to be confusing display text with datatypes. When you type 100:20:55 in Excel it will automatically decided this a date (1/4/1900 4:20:55 AM). This is clearly not what you want because it is NOT text that you typed in. You need to force this to be text instead of the assumed datatype that Excel will utilize. This is not an issue on the sql server side, it is in the import where you need to change this. The bigger issue is that we don't how you are importing this. Are you using the data import wizard in SSMS? An SSIS package?
Just to clarify:
1. I am not typing the values in EXCEL, the time duration and other values in EXCEL are automatically extracted through a different system. I cannot change the data type in EXCEL as when attempted, the time duration values change to some other format which I do not want.
2. I mentioned above as well, I am using the IMPORT DATA wizard in SQL to import data from EXCEL to SQL.
3. I just need the time duration values in EXCEL to be exactly imported across as is to SQL, that's it, even if the value is 100:20:15.
Thanks.
I didn't think you were typing this into Excel. I was trying to explain what happens in Excel.
What is the datatype of the column you are importing to?
Thanks Sean, sorry I misunderstood you. When I first started this thread I hoped to get it resolved quickly but it got complex and frustrating.
I am importing the values from EXCEL to a new table in SQL so I assigned the nvarchar datatype to the associated time duration column in SQL. It works partially correct so when the values are less than 24 hours like 21:10:25, they are imported across as is to SQL as 21:10:25.
The problem starts when the hours value exceed 24 like 100:20:55 in EXCEL. So when this is imported to SQL, it is displayed as 1900-01-01 04:20:55.
Upon assigning the datetime datatype to SQL, the issue still is not resolved as now if the value in EXCEL is 885:39:35 it is displayed as '1900-02-05 21:39:35' in SQL.
I hope I have explained it properly. Thanks.
I understand (and can sense) your frustration. This type of frustration can be avoided by providing details in your questions. The people here are trying to help but getting the details has been like pulling teeth. This is partly due to your frustration but also perhaps because you don't realize that some of this data seems irrelevant but is actually critical to helping you.
The problem you are facing is because the import wizard assumes the source datatype is datetime because that is what Excel has done. I don't think there is a way around this. You will likely need to either use an SSIS package so you can control the source data or do some datemath after importing to update the values to what you want them to be.
I have tried it through SSIS package as well now as you suggested but it still doesn't display the values exactly like in EXCEL.
I really don't understand what data I missed in my first post. ok no worries, I will try some other way or may be jump of the building. Thanks.
In an SSIS package you can control the datatype of the source which is what you are going to have to do. The datatype in EXCEL is a datetime, you have to deal with that at the source. You can't do that in the data import wizard. Another option might be to import the data as it is and then do some date math to get it back in the weirdo string version you need. I say weirdo because if this was my database I would probably store the number of minutes (or whatever level of precision you need) as an int and then do some manipulation to that value when presenting it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 8, 2015 at 11:58 am
pwalter83 (12/8/2015)
ben.brugman (12/8/2015)
pwalter83 (12/8/2015)
Upon assigning the datetime datatype to SQL, the issue still is not resolved as now if the value in EXCEL is 885:39:35 it is displayed as '1900-02-05 21:39:35' in SQL.
I hope I have explained it properly. Thanks.
I have added ,('1900-02-05 21:39:35','1900-02-05 21:39:35'); to the example.
So that you can check the results yourself.
In the last three columns it gets displayed as 861:39:35
ben
Thanks Ben, but I am getting the value as 2925:39:35. I don't know how you managed the value 861:39:35 but that is correct.
Now there is an abundance of HORROR's.
(I am at home at the moment and do not have easy access to SQL-server at the moment, so this is going from memory for a large part).
HORROR 1
First the notation:
'1900-02-05 21:39:35' (For me this is ccyy-mm-dd hh:mm:ss)
But it seems that for your SQL-server it is: ccyy-dd-mm hh:mm:ss
So I think in your system the day and month are represented the other way around.
This is an important reason that date and datetime formats should realy not be exchanged in a string format, because there is always the problem what is what.
Getting the correct settings for Windows/Excel/SQLserver normally does solve this problem. But this is rather cumbersome and mistakes keep happening with this.
HORROR 2
If you get the day and the month correct there is a second HORROR.
In Excel the date after 28 februar 1900 is 29 februar 1900. The problem is that the beginning of the last century there was no leap year.
Correctly SQL-server does not have a 29th of februar 1900, just because in our modern calendar it does not exist.
(Russia, Vietnam and some other countries do have that date though).
This gives another difference of 24 hours in our calculation.
1900-02-05 21:39:35 (minus 1 day) represents 861:39:35 (In Excel)
1900-05-02 21:39:35 (minus 2 days) represents 2925:39:35 (In Excel)
One day of difference because Excel has a 29th of februar.
One day of difference because Excel displays day 0 as 1900-01-00 00:00:00. (And the zero'th of januar does not exist either).
If the duration is imported as a date format or a string format, this 29th of februar problem has to be corrected for.
This is rather complex to do. So my advise is import the value from Excel as an Float and go from there.
In my example I have added some extra rows. There are some extra rows around the februar date and some extra rows around the may date.
Some dates are commented out, because if the settings are in the wrong order those dates do not get accepted.
Ben
December 10, 2015 at 5:51 am
Excel
hours:minutes:seconds yyyy-mm-dd (hh:mm) float (days)
24:00:00 1900-01-01 1.00
32:00:00 1900-01-01 08:00 1.33
1424:00:00 1900-02-28 08:00 59.33
1448:00:00 1900-02-29 08:00 60.33
1472:00:00 1900-03-01 08:00 61.33
885:39:35 1900-02-05 21:39 36.90
2973:39:35 1900-05-02 21:39 123.90
Same dates/times in SQL-server
idthis_date days hours
11900-01-01 00:00:00.0000 0
21900-01-01 08:00:00.0000.3333333333333338
31900-02-28 08:00:00.00058.33333333333331400
41900-03-01 08:00:00.00059.33333333333331424
51900-02-05 21:39:37.00035.9025115740741861.660277777778
61900-05-02 21:39:37.000121.9025115740742925.66027777778
The above shows that there is one day of difference in value for jan and feb 1900.
And that there are two days of difference for all other dates.
The code below shows that 'for input' the date and month can be different in different settings.
The code below shows that the import setting and the output (select of print) be different.
SET LANGUAGE english
exec sp_drop dates
create table dates (
id int identity(1,1)
,this_date datetime)
Insert into dates values('1900-01-01')
Insert into dates values('1900-01-01 08:00')
Insert into dates values('1900-02-28 08:00')
Insert into dates values('1900-03-01 08:00')
Insert into dates values('1900-02-05 21:39:37')
Insert into dates values('1900-05-02 21:39:37')
select *, CAST(this_date as float) days ,CAST(this_date as float)*24 hours from dates
-- Show the order of import of dates depending on the language.
set language english
----------------------------------------------------------------------------
select convert(varchar(32),convert(datetime,'1900-02-05 21:39:37'),121) Englisch_Output_format
set language dutch
-- Same string, but different order of month date.
----------------------------------------------------------------------------
select convert(varchar(32),convert(datetime,'1900-02-05 21:39:37'),121) Dutch_Output_format
-- Output remains the same. Month and Day are NOT reversed.
select *, CAST(this_date as float) days ,CAST(this_date as float)*24 hours from dates
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply