4 Replies - 1767 Views - Last Post: 09 January 2013 - 03:01 PM Rate Topic: -----

#1 IngeniousHax  Icon User is offline

  • |>|20-514<|{3|2

Reputation: 78
  • View blog
  • Posts: 1,362
  • Joined: 28-March 09

MM-YY Format for PostGreSQL

Posted 09 January 2013 - 12:22 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: MM-YY Format for PostGreSQL

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,543
  • Joined: 12-December 12

Re: MM-YY Format for PostGreSQL

Posted 09 January 2013 - 01:27 PM

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 :whistling:/>/>.

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

Was This Post Helpful? 0
  • +
  • -

#3 IngeniousHax  Icon User is offline

  • |>|20-514<|{3|2

Reputation: 78
  • View blog
  • Posts: 1,362
  • Joined: 28-March 09

Re: MM-YY Format for PostGreSQL

Posted 09 January 2013 - 02:15 PM

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
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,543
  • Joined: 12-December 12

Re: MM-YY Format for PostGreSQL

Posted 09 January 2013 - 02:20 PM

View PostIngeniousHax, 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 :whistling: you need to substitute it with a variable.
Was This Post Helpful? 0
  • +
  • -

#5 IngeniousHax  Icon User is offline

  • |>|20-514<|{3|2

Reputation: 78
  • View blog
  • Posts: 1,362
  • Joined: 28-March 09

Re: MM-YY Format for PostGreSQL

Posted 09 January 2013 - 03:01 PM

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)
:gun_bandana: :helpsmilie:

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

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1