PostgreSQL - convert year to year

Question

The year is specified as int : 2009, 2010, etc.

I want to convert this information to DATE (first of January).

My solutions (I prefer the first):

(year::char || '-01-01')::DATE '0001-01-01' + ((year-1)::char || ' year')::interval 

Is there a better (built-in) or more elegant and faster solution?
(I am currently working with PostgreSQL 8.4 , but also interested in later versions.)

Answer 1

I think this is the easiest way:

 to_date(year::varchar, 'yyyy') 

Answer 2

 SELECT to_date(2011::text, 'YYYY'); 

Attention: any code based on casting by default from the text today is bad. Someone might change the default datestyle format to some unexpected value, and this code does not work. Using to_date or to_timestamp is very preferable. to_date or to_timestamp is relatively expensive, but it is stable.

Answer 3

 to_date('01 Jan ' || year, 'DD Mon YYYY') 

OR

 SELECT (DATE (year || '-01-01')) 

ref: http://www.postgresql.org/docs/current/interactive/functions-formatting.html
Note. I did not work with PostgreSQL

Answer 4

One possibility:

 select year * '1 year'::interval + '0000-01-01'::date; 

I like this way because it avoids the conversion between text and integer (as soon as all constants are parsed).