Rails3/jQuery UI Datepicker - Month and day are reversed on save

Question

(My problem is pretty much the same as described in this question, only that answer does not work in my case.)

---

I'm using the jQuery UI Datepicker in a Rails app with a Postgres db. The default implementation uses a mm/dd/yy date format to display the selected date, which is exactly what I want. However, after I save the record to the database, the month and day are reversed - it then displays as yy-dd-mm. So selecting 3/11/2012 tries to save as November 3rd instead of March 11th, and a date like 3/31/2012 is not saved at all, because it does not exist.

I've gone down 3 different avenues so far trying to fix this:

1) First attempt was to reformat the text field so the display looked how I wanted:

<%= f.text_field :foo, :value => (@model.foo.blank? ? '' : @model.foo.strftime('%m/%d/%Y')), class: "datepicker" %>

This displayed 03/31/2012 correctly initially, but still reversed when saving.

2) So next I tried changing the default way that dates are stored, thinking that would get around the problem. As described in the answer to this question, I added the following to config/locales/en.yml:

# config/locales/en.yml
en:
  date:
    formats:
      default: "%m/%d/%Y"

This did not make any difference at all. Next I found this question and tried creating config/initializers/date_formats.rb with these lines:

Date::DATE_FORMATS[:default]="%m/%d/%Y"
Time::DATE_FORMATS[:default]="%m/%d/%Y %H:%M"

Same as above, no difference.

3) After playing around with lots of combinations, the one thing I found that DID work was specifying the yyyy-mm-dd format in my call to the datepicker plugin - it's the opposite of what I wanted, but at least the dates can be successfully saved. So the datepicker call looks like this:

$( ".datepicker" ).datepicker({ dateFormat: 'yy-mm-dd' });

Selecting March 31st from the calendar populates the field with 2012-03-31, and still displays as 2012-03-31 after saving.

But how on earth can I get the datepicker to work with a mm/dd/yy format? I can't imagine that's a difficult thing to do, but what am I missing? Do I have to do something with how the dates are stored in Postgres? (They are specified as date, not datetime.)

Answer 1

Well it depends on how you are storing data in your database. If you happen to use RAW sql queries you can use to_date('31 Mar 2012', 'DD Mon YYYY') in your insert query.

Postgres' to_date reference

Now, on your client side, in the jquery date picker you can use

$.datepicker.formatDate('dd-M-yy', Yourdate);

I used DD Mon YYYY because it seems clearer. You can use mm/dd/yy format or other formats based on the needs. In those cases, use required format in postgre's to_date function

Kindly note, I havent tested above code. Leave comments if there are any issues or you face any troubles.

Edit:

You can use Date.parse('2011-06-18') in your rails view, before inserting data into database and set formatDate to 'yy-mm-dd' in the datepicker.