Re: datestyle question

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.