[IngeniousHax]

Hey all,

I seem to be having a bit of trouble in formatting a certain type of date.

What I do is this:
Read in a CSV file into a staging table
From the staging table insert it into a production table where everything is formatted and converted properly.

The problem I am having is there is one column in every XLS->CSV that has May-00 in it, of which in the staging table is of type varchar and I can't quite figure out how to convert it to a date for the production table... The production tables dates are formatted as such: 'YYYY-MM-DD'

Any ideas?

This post has been edited by IngeniousHax: 09 January 2013 - 01:00 PM

[andrewsw]

Looks like you need to use to_date():

to_date('May 00', 'Mon YY') # or
to_date('May 00', 'Month YY')

http://www.postgresq...formatting.html
This assumes that it will default to the 1st of the month, and that 00 will mean 2000(?). Otherwise,

to_date('01 ' || 'May 00', 'DD Month YY')

I should qualify that I've never used Postgre />/>.

This post has been edited by andrewsw: 09 January 2013 - 01:28 PM

[IngeniousHax]

THanks for the help, but I have already tried that, and I need a fairly generic way of doing it since these types of dates are scattered through the CSV/XLS files

[andrewsw]

IngeniousHax, on 09 January 2013 - 02:15 PM, said:

THanks for the help, but I have already tried that, and I need a fairly generic way of doing it since these types of dates are scattered through the CSV/XLS files

Yes, I didn't mean that you would put the specific value 'May 00' directly in the SQL statement you need to substitute it with a variable.

[IngeniousHax]

Right, I found a nice little thing called tsquery to find what column a value is in, but it doesn't tell me what column it's in... just looks like this:

?column?
--------
5-Mar

1 Row(s)


Also used ts_rewrite to try and replace that nuisance string, but nothing...

This post has been edited by IngeniousHax: 09 January 2013 - 03:06 PM