Hi John, John O'Gorman [mailto:[EMAIL PROTECTED]] wrote: > > Thank you Steffen. That answers my question perfectly. Some small > examples just > like yours would be a valuable addition to the documentation! > Yes, I know. And I've already promised to do. But the time goes by and no time is left for documentation. It'll get a more important issue.
> Topic 2 - loading aberrant DATE fields > > Informix supply a toy database (in their meaning of database: > collection of tables > with data) called STORES. This allows you to load data and play with > SQL. > The stores data is UNLOADed in America, where they apparently > represent dates in > MM/DD/YY format. To get these into SAPDB, I have been writing Perl > scripts to > substite the re patterns for MM/DD/YY into (19|20)YYMMDD. Is > there a way to > get repmcli DATALOAD to do this for me? (If so a minimal example would > be much appreciated!). > Hmm, the problem is the 19|20 stuff. Date patterns in general may be given to commands in 2 ways (this is true for time and timestamp, too): 1. Predefined patterns can be used like USA (MM/DD/YYYY) or ISO (YYYY-MM-DD). Those can either be specified using the 'SET DATE <pattern name>' command (for instance SET DATE ISO) and are valid for the whole command file until reset. Or they can be specified for a certain LOAD/EXTRACT command where they overwrite the global setting like this DATALOAD TABLE <table name> <col name> <pos> ... INFILE '<infile name>' DATE ISO 2. Date patterns different from predefined may only be specified in commands directly. So you would use this command to load MM/DD/YY dates: DATALOAD TABLE <table name> <col name> <pos> ... INFILE '<infile name>' DATE 'MM/DD/YY' Ok, now the problem. Using a 2-digit year representation will always be transformed into 20YY by the Loader server (only partially true - if you're using 7.3 version there is still a bug and Loader server will transform it into 19YY). So is there any possibility to 'convince the Informix tools' to store the dates as 4-digit representation? How did they handle the Y2K bug? > If the date load would be possible except for the 2 digit > year, would it be possible > to load dates represented as ASCII MM/DD/YYYY ? > Hmm, I should read to the very end before answering in detail. Yes it is. Here is the command example from above: DATALOAD TABLE <table name> <col name> <pos> ... INFILE '<infile name>' DATE USA Regards, Steffen -- Steffen Schildberg SAP DB Team SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
