Postgres Pro
Downloads
Home   >   mailing lists

Thread: Create Datefield from 3 fields

Create Datefield from 3 fields

From
Andreas Forø Tollefsen
Date:
Hi.

I am trying to create a datefield using YEAR, MONTH and DAY fields of type integer.
I tried this query, but it did not give good results:
select to_date(gwsyear::text || gwsmonth::text || gwsday::text, 'YYYY-MM-DD') FROM cshapes
"1966526-01-01"
"7991903-12-13"
"1962831-01-01"
"194611-01-01"
"196211-01-01"

Any ideas on how to do this?

Re: Create Datefield from 3 fields

From
Thomas Kellerer
Date:
Andreas Forø Tollefsen, 06.10.2010 13:11:
> Hi.
>
> I am trying to create a datefield using YEAR, MONTH and DAY fields of type integer.
> I tried this query, but it did not give good results:
> select to_date(gwsyear::text || gwsmonth::text || gwsday::text, 'YYYY-MM-DD') FROM cshapes

You are missing the dashes in the input string that you specify in the format mask
  to_date(gwsyear::text ||'-'|| gwsmonth::text ||'-'|| gwsday::text, 'YYYY-MM-DD')


Thomas

Re: Create Datefield from 3 fields

From
Andreas Forø Tollefsen
Date:
Excellent. I did not see that.

Thank you

2010/10/6 Thomas Kellerer <spam_eater@gmx.net>
Andreas Forø Tollefsen, 06.10.2010 13:11:

Hi.

I am trying to create a datefield using YEAR, MONTH and DAY fields of type integer.
I tried this query, but it did not give good results:
select to_date(gwsyear::text || gwsmonth::text || gwsday::text, 'YYYY-MM-DD') FROM cshapes

You are missing the dashes in the input string that you specify in the format mask


 to_date(gwsyear::text ||'-'|| gwsmonth::text ||'-'|| gwsday::text, 'YYYY-MM-DD')


Thomas


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql