storing only time in sql server 2005

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

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-

Hi,

Try this.

SELECT CONVERT(VARCHAR(8),FieldName,108)

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 .

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

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
);

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

The only isssue with loading the total_duration column where by default it takes timestamp of 1900 year ...

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

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.

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.

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)
);

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?

Hi,

check out this link.

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

You can import using openrowset.


here you can customize the query also.

You can use

total_duration varchar(10) null,

You can check out this article too.

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_3025-Retrieving-Data-From-Excel-Using-OPENROWSET.html

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.

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

Select allOpen in new window

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.

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.

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

Select allOpen in new window

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.

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

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?

I wan to store as 0:00:00 boz it the total duration which means that no calls by the rep.

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

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)".

Ok.

Do one thing.

Make all columns datatype varchar(50)

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

cant you replace with some other character???

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.

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.

Thanks for reopening and making my work too easy ..

Cheers