Solved

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

Posted on 2002-03-12
9
1,165 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
  • 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
 
LVL 5

Expert Comment

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

plz let me know.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 98 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Syntax 6 382
surrogate key in database world 6 73
Need help with SYBASE syntax 5 47
Not able to use a TRUNCATE command in 4 35
Hyena v12.2 is now available for downloading and is available in English, French, German and Spanish versions.
What is Backup? Backup software creates one or more copies of the data on your digital devices in case your original data is lost or damaged. Different backup solutions protect different kinds of data and different combinations of devices. For e…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

759 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now