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.)
I think this is the simplest way:
to_date(year::varchar, 'yyyy')
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.
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
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With