Thread: Newbie Date Problems

I've been working with Postgres for about 2 years now.  I've never had to
do anything real advanced until now and I'm having a huge problem with
some dates.  A long time ago, I thought it would be a good idea to have
dates in 4 fields:

date = 5/2/2002
date_month = 5
date_day = 2
date_year = 2002

This was so I could view everyone in May, or everyone between the 10th and
15th, or everyone in 2002, etc... This was a huge mistake.  I have since
learned how to use date fields.  The problem is, I created a database a
while back that has over 800 records in it that uses this style of dates.
I want to convert this format to the ISO date format (YYYY-MM-DD) and I'm
having lots of problems.  I tried using this update:

update table set date_iso = (date_year || '-' || date_month || '-' ||
date_day) where table_id='838

That formats my 3 unnecesary fields to YYYY-MM-DD format.  The problem is,
the month/days are entered as 1, 2, 3, 4, etc... Not 01, 02, 03, 04, etc.
I get the error message:

Attribute 'intake_date_raw' is of type 'date' but expression is of type
'varchar' You will need to rewrite or cast the expression.

Yes, I was stupid and made the date_month, date_day, and date_year
varchars.  How can I update the database and use the current varchar
fields to create a new field formatted for dates?

-----------

Also... it looks like the search utility on the archives aren't working...

An error occured!
could not connect to server: Connection refused Is the server running on
host db.postgresql.org and accepting TCP/IP connections on port 5433?

John,

> update table set date_iso = (date_year || '-' || date_month || '-' ||
> date_day) where table_id='838

You'll appreciate the fix, which takes less time to write than you took
to write your question:

You need to use the to_char function to format your date fragments as
strings with the proper number of digits:

ltrim(to_char(date_month, '00'))

See "formatting functions" in the docs (under "functions and
operators") for more help.

-Josh Berkus

Thanks for the help... I checked the formatting functions section and it
helped a lot.

update clients set date_raw = to_char(timestamp (date_year || '-' ||
date_month || '-' || date_day), 'YYYY-MM-DD') where clients_id='835';

That seems to work...

John

On Thu, 9 May 2002, Josh Berkus wrote:

> John,
>
> > update table set date_iso = (date_year || '-' || date_month || '-' ||
> > date_day) where table_id='838
>
> You'll appreciate the fix, which takes less time to write than you took
> to write your question:
>
> You need to use the to_char function to format your date fragments as
> strings with the proper number of digits:
>
> ltrim(to_char(date_month, '00'))
>
> See "formatting functions" in the docs (under "functions and
> operators") for more help.
>
> -Josh Berkus
>