How about creating an extra column and a before insert trigger to solve
this?



1. In addition to the Timestamp column, add a RawDate Char(8)
column
2. Then write a before insert trigger that parses RawDate using the
SubStr function that I believe is in the standard distribution to pull
the year, month, and day out of the raw column.
3. Use Convert plus a couple of string concats to drop a real date
into the Timestamp column



I don’t know if this invalidates your test, but it will import the file
you indicated relatively simply.



Louis Kleiman

SSTMS, Inc.



-----Original Message-----
From: clementdoss [mailto:cdoss@...]
Sent: Wednesday, October 30, 2002 9:44 PM
To: ib-support@yahoogroups.com
Subject: [ib-support] Re: Simple challenge



I would like to thanks all for the help!

I will try to answer to everyone...

To svein : I must use Timestamp simply because one of the RDMS don´t
have Date only field type.

I am using IBO Script to to commit every 5000 rows. This was the best
approach so far.

I must keep the extact same script because later it will be used in
queries to display results, after some updates/deletes/insert stuff.
And obviously, the results reported from all the RDMS must be same.

When I read Ann´s 122 seconds <g> I fell off my chair!!!
Is it possible to read a file one row at a time and inset the info
using QLI? If that´s is possible, I sure no one will beat this one!
The only thing here is that I must keep the data in the same format
they sent me...

By the way, would it be too hard to get an UDF (or something alike) to
convert a Date using a mask. SQL Server has a convert function that is
nice. Here is some info about it...

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Without Century : 1,2,3,4,5,6,7,8,10,11,12,14
With century : 0 or 100,101,102,103,104,105,106,107,108,9 or 109,...

1 / 101 --- USA --- mm/dd/yy
2 / 102 --- ansi-- yy.mm.dd
3 / 103 -- british/French -- dd/mm/yy
12/ 112 -- ISO -- YYMMDD
The usage is simple

Select convert( varchar(10) , current_timestamp , 3 )

will return 30/10/02

Select convert( varchar(10) , current_timestamp , 103 )

will return 30/10/2002 (not the year format YYYY)


select convert( datetime , '20021030' , 112 ) -- Will convert
YYYMMDD to datetime

will return 2002-10-30 00:00:00.000 (SQL default output)

(This was extract from SQL BOL. If you need more info just let me know)

Best regards,
Clément






Yahoo! Groups Sponsor



ADVERTISEMENT

<http://rd.yahoo.com/M=237459.2482214.3917349.2146399/D=egroupweb/S=1705
115386:HM/A=1267611/R=0/*http:/ad.doubleclick.net/jump/N2524.Yahoo/B1071
650;sz=300x250;ord=1036032237718769?>



<http://us.adserver.yahoo.com/l?M=237459.2482214.3917349.2146399/D=egrou
pmail/S=:HM/A=1267611/rand=404445498>


To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



[Non-text portions of this message have been removed]