CODEWITHSUNDEEP
postgresqldate
krishnab
krishnab

Reputation: 10060

Convert 2-digit date to year in the 1900s

I am using the string manipulations functions in PostgreSQL 9.6 to convert a 2-digit text year into a 4 digit year. I am able to convert the string into a date and year, but the I keep getting years in the 2000s instead of the 1900s. 

select extract(year from to_date('58', 'YY'));

This returns 2058 instead of 1958.

Now all of my 2 digit dates are in the 1900s, but I cannot find a max function or parameter in the to_date() function that forces the date into the 1900s.

Anyone know how to change this behavior?

Upvotes: 2

Views: 2946

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656706

Exact behavior is defined in the manual here:

If the year format specification is less than four digits, e.g. YYY, and the supplied year is less than four digits, the year will be adjusted to be nearest to the year 2020, e.g. 95 becomes 1995.

Bold emphasis mine.

Since 2058 is closer to 2020 than 1958, the first one is the result you get from to_date('58', 'YY').

There is currently no setting to override this behavior. You have to provide the desired century explicitly if you disagree with the default. Similar to what Haleemur commented:

SELECT to_date('19' || <2-digit-year-variable>, 'YYYY');

But to_date() takes text input, not integer. Integer input would raise an exception like:

ERROR:  function to_date(integer, unknown) does not exist

And if by:

convert a 2-digit text year into a 4 digit year

... you mean a 4 digit text year, it's cleaner to use to_char():

SELECT to_char(to_date('19' || '58', 'YYYY'), 'YYYY')

to_char() can return text in variable formats (unlike extract(), which returns a double precision number).

If the input is valid 2-digit strings (nothing but exactly 2 digits), a simple concatenation does the job, too

SELECT '19' || <2-digit-year-variable>

Upvotes: 2

Haleemur Ali
Haleemur Ali

Reputation: 28253

to convert a 2 digit year stored as text to a 4 digit year like 19xx you can do something like this:

SELECT ('19' || '58')::int

or user klin suggested

select 1900+ '58'

Upvotes: 1

Related Questions