changing timestamp and date format while loading

Hi,

I'm having a column with timestamp datatype and in my csv file the format is in the following format: 21-DEC-07 12.00.00.000 AM

Is there any possible way to change the date format and timestamp format while loading the data using COPY command:

Here is the following error i'm facing with:

test=# create table t1(col1 timestamp);
CREATE TABLE

test=# insert into t1 values('21-DEC-07 12.00.00.000 AM');
ERROR: invalid input syntax for type timestamp: "21-DEC-07 12.00.00.000 AM"

test=# select current_timestamp;
now
--------------------------------
04/09/2008 12:07:26.335413 IST

Quick response can help me to move forward.

Thanks,
Venkat

Hi Venkat,

Unfortunately, neither GridSQL nor PostgreSQL does not support this format.

I think the easiest thing to do is to format it correctly when extracting from the source database in a SELECT statement. The particular source RDBMS may even have a convenient function for you to do so.

Barring that, you could write a small awk or perl script to preprocess your load file and reformat, which should not be too painful. The tricky part would be deciding if the year should append "19" or "20" to the century to get it in YYYY-MM-DD ... etc. format

Regards,

Mason Sharp

This message was edited 1 time. Last update was at 8 Sep 2008 08:12:40

Thanks Mason,

Also i'm having one more problem , i.e., some bad data is coming along with my csv file. Is there any alternate method for PostgresPlus 8.3 where i can load the perfect data and wirte bad data to some file.

Thanks,
Venkat

Hi Venkat,

For error handling, I thought it would make sense for this to create a new thread:

http://forums.enterprisedb.com/posts/list/0/1432.page#5067

Regards,

Mason