CODEWITHSUNDEEP
postgresql
FloE
FloE

Reputation: 1176

PostgreSQL - Convert year to date

The year is given as int: 2009, 2010 etc.
I want to convert this information to DATE (first January).

My solutions (I prefer the first one):

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

Is there a better (build in) or more elegant and faster solution?
(I'm currently working with PostgreSQL 8.4 but are also interested in more recent versions.)

Upvotes: 9

Views: 8513

Answers (4)

Pavel Stehule
Pavel Stehule

Reputation: 45770

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

Attention: any code based on default casting from text to date is bad. Somebody can change a default format datestyle to some unexpected value, and this code fails. Using to_date or to_timestamp is very preferable. to_date or to_timestamp is relative expensive, but it is rock robust.

Upvotes: 4

araqnid
araqnid

Reputation: 133432

One possibility:

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

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

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33476

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 haven't worked with PostgreSQL

Upvotes: 2

everag
everag

Reputation: 7662

I think this is the simplest way:

to_date(year::varchar, 'yyyy')

Upvotes: 16

Related Questions