Solved

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

Posted on 2002-03-12
9
1,176 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When converting a physical machine to a virtual machine using VMware vCenter Converter Standalone or vCenter Converter Enterprise, if an adapter type is not selected during the initial customization the resulting virtual machine may contain an IDE d…
Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

15 Experts available now in Live!

Get 1:1 Help Now