Solved

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

Posted on 2002-03-12
9
1,181 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports VB6 11 1,056
StorageCraft ShadowProtect Sybase VSS? 3 586
the error when installation Sybase ASE 15.7 x64 bon Windows 37 2,859
sql statement error sybase 2 49
This tutorial shows how to create a greeting card by combining two image layers and a text layer on a PC using a free image editing app.
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

772 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