El jue, 27-09-2007 a las 10:32 +0200, Alban Hertroys escribió: > Diego Gil wrote: > > Hi, > > > > I have a file to import to postgresql that have an unusual date format. > > For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without any > > separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't know how > > to handle the DDMMYYYY dates. > > You could try importing those fields in a text field in a temporary > table and then convert them from there into your final tables using the > to_date() function. > > If 20022007 really means 20 Jan instead of 20 Feb, try something like: > No, it realy means 20 Feb. My mistake !. > insert into my_table (my_date_field) > select to_date(my_date_text_field, 'DDMMYYYY') - interval '1 month' > from my_temp_table; > > Regards, I finally ended coding a dirty C program to reverse the order of date fields. Here is the code, in case anyone need it. #define _GNU_SOURCE #include <stdio.h> #include <string.h> #include <stdlib.h> int main(void) { FILE * fp, *f2, *f3; char * line = NULL; char * field = NULL; size_t len = 0; ssize_t read; int fc = 1; fp = fopen("trxs.exp", "r"); f3 = fopen("trxs.ok", "w"); if (fp == NULL) exit(EXIT_FAILURE); while (getline(&line, &len, fp) != -1) { fc = 1; while ((field = strsep(&line, "\t")) != NULL) { if (fc > 1) fprintf(f3, "\t"); if (strlen(field) == 0) { fprintf(f3, "\\N"); } else if ( (fc == 9 || fc == 11 || fc == 12 || fc == 14 || fc == 16) && strlen(field) >= 1) { fprintf(f3, "%c", field[4]); fprintf(f3, "%c", field[5]); fprintf(f3, "%c", field[6]); fprintf(f3, "%c", field[7]); fprintf(f3, "-"); fprintf(f3, "%c", field[2]); fprintf(f3, "%c", field[3]); fprintf(f3, "-"); fprintf(f3, "%c", field[0]); fprintf(f3, "%c", field[1]); } else { fprintf(f3, "%s", field); } fc++; } } fclose(fp); fclose(f3); if (line) free(line); if (field) free(field); return EXIT_SUCCESS; } /* fc means "field count", only fields 9,11,12,14 and 16 are date fields. */ Thanks for all suggestions. Regards, Diego. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend