storing only time in sql server 2005

pinkuray
pinkuray used Ask the Experts™
on
Hello Experts,

I have a table where I need to store only time values like  :

Time
--------------
00:22:34
01:33:32
04:33:22

I am getting the data in bulk with other columns too but when I loada the XLS file into SQL Server by default a date is getting uploaded of 1900-01.... along with my time data.

I wan to get rid  of this as everytime get the data in bulk .
So is there any possible way to handel this while inserting the data into tables.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try setting the type of the timecolumn to varchar(8)

Author

Commented:
I tried with varchar(8) but the data is getting inserted with like below:

1900-01-
1900-01-
1900-01-
1900-01-
1900-01-
1900-01-
1900-01-
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
Hi,

Try this.

SELECT CONVERT(VARCHAR(8),FieldName,108)
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
The result is :   1900-01-
after running convert query.

I wan to restrict this while inserting the data into the table, I dont want to manually or by running any convert method.
While inserting the data into table I should be able to insert only time data i.e. like :

Time
--------------
00:22:34
01:33:32
04:33:22

Having the data in an excel file .
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
Hi,


This will surely work.

SELECT CONVERT(VARCHAR(8),convert(datetime,FieldName,103),108)

While inserting the data into table I should be able to insert only time data
- How do you inserting data??whats the process

Author

Commented:
I created the table and then loading the file to the created table :

create table pstatus
(
Team nvarchar(255),
rep nvarchar(255),
total_calls float null,
total_duration varchar(8) null,
date varchar(8)null
);

Author

Commented:
Please find the attached data where I wan to insert to a new table .

This table has to be created then I should load the excel file into the database.
Test-data.xls

Author

Commented:
The only isssue with loading the total_duration column where by default it takes timestamp of 1900 year ...
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
I'm asking how you going to insert.?
I mean you inserting through queries or some wizard?

Author

Commented:
I am importing the data through MS SQL Server Management Studio Wizard ...
by going to task --> import data --> excel source --> target table .

This is how I import the data.
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
Ok.

Actually in excel, Datatype of Total_Duration is TIME.
so sql converts into datetime,

one way is, change the format of datatype in excel.Make it as Text. then it should store your data properly.

Author

Commented:
OK So what is the structure of the table as like below? or do I need to change anything ?

create table pstatus
(
Team nvarchar(255),
rep nvarchar(255),
total_calls float null,
total_duration nvarchar(255) null,   --- what i need to giev the datatype here ?
date varchar(10)
);

Author

Commented:
I formated the data in excel as text but whe I load it to the table with structure as below:
create table phone_status
(
Team nvarchar(255),
rep nvarchar(255),
total_calls float null,
total_duration varchar(8) null,
date varchar(10)
);


total_duration
10
10
10
10
10.00055
10.00145
10.11229
10.05069
10.05569


-----------------------------------
Or any other way to do this?

Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
Hi,

check out this link.

http://support.microsoft.com/kb/321686

You can import using openrowset.


here you can customize the query also.
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:

You can use

total_duration varchar(10) null,
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:

Author

Commented:
I dont have this permision :
Error while executing as linked excel db:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
hi,

check out 2nd link.
that will surely helps u.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Open in new window

Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
hi..

you no need to accept solution till it get finish.
Please re-open the Question and keep open till u r not satisfied.


I'm requesting moderator to do so.
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:

Dear Author,

Expert-Exchange is here to turned your face from :-( to :-)

So keep updating us.

Once you get the answer then close the questions.

In case of you need urgent assistance then click on "Request Attention" link.

Higher expert will show you the way.

Author

Commented:
Thanks for your comments.....

After running the command in SQL Server I got the below error:

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 1
You do not have permission to run the RECONFIGURE statement.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
Msg 5812, Level 14, State 1, Line 1
You do not have permission to run the RECONFIGURE statement.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Open in new window

Author

Commented:
I created the table as below:

create table pstatus
(
Team nvarchar(255),
rep nvarchar(255),
total_calls float null,
total_duration float null,
date varchar(10)null
);

And made the column as text in Excel and loaded it ....

Then when I query like the below statement I am getting the data as like below:
SELECT CONVERT(varchar(8),convert(datetime,total_duration,103),108)
from pstatus;

I get the result :

Output
-----------------

00:00:48
00:02:06
02:41:42
01:13:00
01:20:12
02:19:12
03:53:29
01:42:05
01:56:36
02:36:06

In the similar fasion I want to store in DB .  So that after wards I can make this into report for many calculation work.



Author

Commented:
All the insert is going wrong due the data i.e. is there in excel file.

As if you see the total duation 1st row having the column value with "0:00:00" which is correct data but if you click and check in excel file it show a different data i.e. 1/10/1900  12:00:00 AM.

Due to this issue I am not able to insert this sucessfully
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:

0:00:00 is not right?

When Timeformat is 24 hours then it will come like that only.

which format u wanted to store the data?

Author

Commented:
I wan to store as 0:00:00 boz it the total duration which means that no calls by the rep.
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
One minute.

Forget everything till now what we talking.

Follow below steps and let me know your feedback.

1. Convert your excel file into Comma Separated Format.

2. Copy the file on database server any location.

3. Run below code.you will get 0:00:00

BULK INSERT pStatus
FROM 'd:\test.csv'
WITH
  (
     FIELDTERMINATOR =','
  )
     
 


here I attached sample file for your reference

test.csv

Author

Commented:
As per your code I placed the file in my server and run the query by just changing the path but still got errors:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (total_duration).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (total_duration).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 4 (total_duration).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 4 (total_duration).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 4 (total_duration).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 4 (total_duration).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 4 (total_duration).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 4 (total_duration).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 4 (total_duration).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 4 (total_duration).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 4 (total_duration).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
Ok.

Do one thing.

Make all columns datatype varchar(50)

Author

Commented:
Bravo I get it but one more addition on this


BULK INSERT pstatus
FROM 'C:\Phone_Data\formated\PhoneData.csv'
WITH
  (
     FIELDTERMINATOR =',' OPTIONALLY = "'"
  )

Also need to add OPTIONALLY ENCLOSED BY " ' "

As another file have name like  O'Chuida  to aviod any collision on this ..
The final one where I can close this
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
cant you replace with some other character???

Author

Commented:
Nope .... The Name will come with like some below example:

Ray, William
Mike, Victoria
O'Chuida
Christine, Joe

So now you have " ,"  and  "  '  "  .

If this is fixed then I can close this request.
Lead Analyst
Top Expert 2010
Commented:
thats where bulk insert get problems.
other method are length where you need to convert delimiter.
else wat you can do is.
replace all comma with ~ and upload the file.
after uploading run following query.

update pstatus
set rep = replace(rep,'~',',')
where charindex(',',rep) > 0

Author

Commented:
Great this helps me......

I am able to load the data into SQL Server.....
Now you can also chekc another question that I have posted for haveing a procedure to be designed on this data.

Author

Commented:
Thanks for reopening and making my work too easy ..

Cheers

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial