?
Solved

Can I convert a string "DD:MM:YYYY" to a DATETIME field during a bcp ?

Posted on 2002-03-12
9
Medium Priority
?
1,198 Views
Last Modified: 2012-06-27
Hi,

I've got a comma seperated file ...
i.e.
123,25:12:2001
124,26:12:2001
....
i.d,dd:mm:yyyy.

My table is like so...
ID    SMALLINT
BDay  DATETIME

Task:
I need to 'bcp' the file containing these rows of comma seperated fields with '\n' as a row terminator.

Problem:
When I try...
'bcp db.owner.table in file -U -P -c -t , -r \\n'
It will not work as I get an error on the second field (the dd:mm:yyyy). I know this date field should be like yyyy:mm:dd but this 'cannot' be changed !

Is it possible during my bcp to actually convert this string dd:mm:yyyy into a format that can be inserted into a Sybase DATETIME type ?

Remember, I need to bcp the whole file (i cannot read each line of the file and convert the date string) as is.
Also, the field in the database needs to be a DATETIME, I can't just use a char here as I need to be able to sort by date (and I can't sort on dd:mm:yy) :(

Cheers,
Brian.


0
Comment
Question by:brianon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6857506
if you are on a unix platform, i can help you with a script that will convert that field to the format you want and then you can bcp that file.

if you are familiar with perl, you can write a script for urself or i guess that could be done in MS Excel also ..

let me know if can help u
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6858618
brianon,

A suggestion to skip scripts and do everythign through SQL

create a table similar to the existing one, just change the datatype of that particular column from datetime to char(n).

then bcp this file into this new table.

now once data is in the new table, just update that column using characteristic function into the format u want.

finally .. just insert into the old table from the new table.
 
(remember to convert to datetime this char column)

cheers
0
 

Author Comment

by:brianon
ID: 6859922
Thanks guys.

However, what I'm really looking for is a way to convert the char(mm:dd:yyyy) to the Sybase DATETIME 'during' the bcp of the comma seperated file to the Database.

I don't think the script would work as time/speed of execution is a big factor in this one.

The extra table option ??? Not sure but I'll think about it.

Cheers,
Brian.

0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6860668
u work on unix platform or ms windows

plz let me know.
0
 

Author Comment

by:brianon
ID: 6860682
I work on a unix platform.
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6860687
ok that a good thing - now i can provide you with some script that will convert this format into the format that is accepted by Sybase.

Could you please paste here a sample line of the file.

thanks.
0
 
LVL 3

Expert Comment

by:knel1234
ID: 6877457
brianon,

I think amit really has your answer.  A simple external program (c or perl) can convert 100K rows in a few seconds.  Of course, this does depend on your system.  Amit's thoughts about using a temp table are a good idea too.   You could put the logic in a Stored Procedure and then let sybase do all the work.  Just make sure to keep an eye on your log space as well as your indexes when you do this(you will be doing an insert/select and not a bcp and this will effect log usage.  You may need to drop all/most of your indexes and then recreate them. Note: this normally should have happened even if you just were to bcp directly into the table.  You might need to do some experimenting with this as this depends on the number of rows in the table vs number of rows being imported.  If you get a sample line (or just dummy up one), then im sure amit can get you what you need.

knel
0
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 294 total points
ID: 6900410
brianon - any updates on this question ?
0
 

Author Comment

by:brianon
ID: 6901957
thanks for your help.
It solved half the problem and I found the rest myself.
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.
Active Directory can easily get cluttered with unused service, user and computer accounts. In this article, I will show you the way I like to implement ADCleanup..
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month11 days, 12 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question