pgSQL : insert date format (leading zeros)?

Question

I want to insert a date into my pg database, but I haven't got a clue what the correct format is and the pg help isn't really helping.

I have my date from form in d-m-yyyy format. So leading zeros are omitted.

How can I insert this correctly, is there a function to add leading zeros (either pg or php) ?

Solution 1

INSERT INTO TheTable (the_date) VALUES ('yyyy-mm-dd')

is the correct order in SQL

$psql_str = date('yyyy-mm-dd', date_parse_from_format('d-m-yyyy', $date_str));

converts your $date_str to the expcted format.

Solution 2

Check to_date(text, text) function (Table 9-21 contains all supported patterns):

SELECT to_date('1-9-2011', 'DD-MM-YYYY');
  to_date   
------------
 2011-09-01
(1 row)

As you see leading zeros are properly added in output date.