[SOLVED] Interpreting 2 digit years

[artacus]

So I'm importing data from this really old system, circa 1985. So of course it has 2 digit years for all of the dates.

 

I'm using mysql's STR_TO_DATE() to convert these to dates, but the default is to interpret any 00 - 69 as 2000 - 2069 ... not what I need for birthdays. Now its easy enough to subtract 100 years for anyone born before today. But is there a variable I can set at the connection level, to change how it interprets 2 digit years? I'd like not to have to change the setting for the whole server.

[btherl]

The manual is pretty unambiguous .. it doesn't mention any settings that can be altered.

 

If it's a once-off import, why not just do it the easy way?  I don't quite get what you mean by "subtract 100 years for anyone born before today", but it's simple enough to insert "19" in front of each year before calling str_to_date().

[artacus]

I'm positive that I read somewhere that you could change where mysql interprets a 2 digit year, at least at the server level. But I can't for the life of me find it now.

 

I don't quite get what you mean by "subtract 100 years for anyone born before today"

So if John Doe was born Jun 1, 66 his bday would read 060166, mysql will interpret this as 06/01/2066. So you have to subtract 100 years. The problem is there are lots of other dates in this extract (hire date, termination date, etc.) So prepending 19 to all of the years would not work there. And again some employees have been here pre 1970. So the easiest way to fix it all would be to just tell mysql to interpret anything above 08 as 1900's and anything below as 2000's.

[fenway]

And why can't you use something like this:

 

select str_to_date( CONCAT( LEFT( '060166', 4 ), IF( RIGHT( '060166', 2 ) > 8, '19', '20' ), RIGHT( '060166', 2 ) ),  '%m%d%Y' )

[artacus]

That will work. I just thought it would be easier to spend several hours googling the mysql documentation.

 

BTW I think it should be

... IF( RIGHT( '060166', 2 ) > '08', '19', '20' )...

because of the way it compares text '10' < '8'

[fenway]

You're probably right... I didn't test it very thoroughly.