December 8, 2015 at 3:42 am
Hi,
Can somebody please advise on how to use the convert function within the create table statement ?
I need to use the below conversion code suggested by one of the members.
CONVERT(VARCHAR(10),DATEDIFF(hh,0, [Time Elapsed])) + RIGHT(CONVERT(CHAR(8), [Time Elapsed],108),6)
Thanks.
December 8, 2015 at 3:46 am
What do you mean? Are you trying to include a computed column in a new table definition? Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2015 at 4:13 am
GilaMonster (12/8/2015)
What do you mean? Are you trying to include a computed column in a new table definition? Something else?
Thanks Gail. Yes, I want to add a computed column in the create table statement. How can I ad the below computed column within the create table statement:
CONVERT(VARCHAR(10),DATEDIFF(hh,0, [Time Elapsed])) + RIGHT(CONVERT(CHAR(8), [Time Elapsed],108),6) as [Time Elapsed]
Thanks.
December 8, 2015 at 4:18 am
https://msdn.microsoft.com/en-za/library/ms188300.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2015 at 4:30 am
GilaMonster (12/8/2015)
https://msdn.microsoft.com/en-za/library/ms188300.aspx
I have already gone through this link and it doesn't mention anything about using the convert function in the create table. I don't even know if its possible to do that ? Do you know how to achieve this ? Thanks.
December 8, 2015 at 4:41 am
Can somebody help on this please ? I have already gone through tutorials and links and really need a straight forward answer to this if possible.
December 8, 2015 at 4:53 am
pwalter83 (12/8/2015)
Can somebody help on this please ?
Yes. Take the example given on that page and modify it with your table name, columns names and computation. Post back if you get any errors you don't understand.
John
December 8, 2015 at 5:06 am
John Mitchell-245523 (12/8/2015)
pwalter83 (12/8/2015)
Can somebody help on this please ?Yes. Take the example given on that page and modify it with your table name, columns names and computation. Post back if you get any errors you don't understand.
John
I have already tried that and other stuff as well.
There are no errors, it simply doesn't work as I want the time duration excel column value to be copied over to sql exactly without getting converted.
As the value is getting converted if its over 24 hours, I thought about using a computed column in sql to get the desired format. However, the convert function doesn't work as well.
December 8, 2015 at 6:30 am
Examples of a computed column in a table.
Source for the computed column in this case is varchar or datetime.
In excel time and/or date is just presented as the number of days passed since 1899/12/30.
(Except for jan and feb in 1900 then it is the number of days passed since 1899/12/31).
So time and date are not 'stored' as time and date in Excel, but as a floating point number.
Depending on the importing technique used, you get different results in SQL-server.
Below a number of examples using computed columns to get the time out of such an import.
(String3 is obtained using an update, others use computed column constructions.)
Does this help?
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
, 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)
);
-- Insert values into the table.
INSERT INTO dbo.import_time (time_in, time_in2)
VALUES ('11:30','11:30')
,('20151205 12:30','20151205 12:30')
,('20131205 13:30','20131205 13:30')
,('19000101 14:30','19000101 14:30');
update import_time set time_string3 = substring(convert(varchar(30), time_in2, 126),12,5)
-- Display the rows in the table.
SELECT * FROM dbo.import_time;
December 8, 2015 at 7:50 am
ben.brugman (12/8/2015)
Examples of a computed column in a table.Source for the computed column in this case is varchar or datetime.
In excel time and/or date is just presented as the number of days passed since 1899/12/30.
(Except for jan and feb in 1900 then it is the number of days passed since 1899/12/31).
So time and date are not 'stored' as time and date in Excel, but as a floating point number.
Depending on the importing technique used, you get different results in SQL-server.
Below a number of examples using computed columns to get the time out of such an import.
(String3 is obtained using an update, others use computed column constructions.)
Does this help?
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
, 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)
);
-- Insert values into the table.
INSERT INTO dbo.import_time (time_in, time_in2)
VALUES ('11:30','11:30')
,('20151205 12:30','20151205 12:30')
,('20131205 13:30','20131205 13:30')
,('19000101 14:30','19000101 14:30');
update import_time set time_string3 = substring(convert(varchar(30), time_in2, 126),12,5)
-- Display the rows in the table.
SELECT * FROM dbo.import_time;
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.
December 8, 2015 at 7:59 am
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.
December 8, 2015 at 8:28 am
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.
December 8, 2015 at 8:42 am
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.
People here are trying to help you but it doesn't appear like you are trying to help yourself. If you would just post your code along with any errors you would have had a solution to your problem by now. You keep saying you have tried it but where is your code?
It would appear that you took somebody else's code/suggestion without realizing what it actually does. Also it was already explained in another post that you can't have more than 24 hours in a datetime field. What you have is a value that represents how much time has gone by NOT an actual date.
I'll use a previous example from your other thread to show that something like this works fine and thus there is no problem using a CAST or CONVERT in a create table statement.
CREATE TABLE #test
(
customInterval VARCHAR(10),
secondsInterval AS (CAST(REPLACE(customInterval,':','') AS INT) / 10000 * 3600) + (CAST(REPLACE(customInterval,':','') AS INT) / 100 % 100 * 60) + (CAST(REPLACE(customInterval,':','') AS INT) % 100)
)
INSERT INTO #test (customInterval) VALUES ('100:20:30')
SELECT * FROM #test
DROP TABLE #test
Unfortunately, that isn't your problem. The real issue is that your are unable to help us help you. People can't and won't do the work for you.
Read this article on how to post examples: Click Here[/url]
December 8, 2015 at 9:00 am
yb751 (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.People here are trying to help you but it doesn't appear like you are trying to help yourself. If you would just post your code along with any errors you would have had a solution to your problem by now. You keep saying you have tried it but where is your code?
It would appear that you took somebody else's code/suggestion without realizing what it actually does. Also it was already explained in another post that you can't have more than 24 hours in a datetime field. What you have is a value that represents how much time has gone by NOT an actual date.
I'll use a previous example from your other thread to show that something like this works fine and thus there is no problem using a CAST or CONVERT in a create table statement.
CREATE TABLE #test(
customInterval VARCHAR(10),
secondsInterval AS (CAST(REPLACE(customInterval,':','') AS INT) / 10000 * 3600) + (CAST(REPLACE(customInterval,':','') AS INT) / 100 % 100 * 60) + (CAST(REPLACE(customInterval,':','') AS INT) % 100)
)
INSERT INTO #test (customInterval) VALUES ('100:20:30')
SELECT * FROM #test
DROP TABLE #test
Unfortunately, that isn't your problem. The real issue is that your are unable to help us help you. People can't and won't do the work for you.
I don't know what code are you talking about. My issue from the other thread is below:
I am trying to import data from MS EXCEL to a new table in SQL using 'IMPORT DATA' functionality in SQL in the 'TASKS' menu and there is one particular time duration column - [TIME ELAPSED] in EXCEL where there are values like 100:20:55. When this value gets imported to SQL, the value is displayed as '1900-01-01 04:20:55' if the datatype for TIME ELAPSED in SQL is nvarchar.
if the TIME ELAPSED value in EXCEL is below 24 hours like for e.g. 21:10:15, then it is exported across exactly in SQL. Its only when the value of the hours crosses 24 then I get values like 1900-01-01.
To overcome this I changed the datatype of the TIME ELAPSED column in SQL to datetime but the result is still the same and now for the hours below 24 like 00:32:01, it shows the value as 1899-12-30 00:32:01.
I also tried to add a computed column - TIME_ELAPSED with the following code in the create table statement:
CONVERT(VARCHAR(10),DATEDIFF(hh,0,[TIME ELAPSED])) + RIGHT(CONVERT(CHAR(8),[TIME ELAPSED],108),6)
However, now I get incorrect values in the computed column in SQL due to incorrect code above.
So that is my main issue if I am able to explain correctly now. Thanks.
December 8, 2015 at 9:12 am
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?
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply