artacus Posted February 26, 2007 Share Posted February 26, 2007 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. Link to comment https://forums.phpfreaks.com/topic/40249-solved-interpreting-2-digit-years/ Share on other sites More sharing options...
btherl Posted February 27, 2007 Share Posted February 27, 2007 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(). Link to comment https://forums.phpfreaks.com/topic/40249-solved-interpreting-2-digit-years/#findComment-195032 Share on other sites More sharing options...
artacus Posted February 27, 2007 Author Share Posted February 27, 2007 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. Link to comment https://forums.phpfreaks.com/topic/40249-solved-interpreting-2-digit-years/#findComment-195344 Share on other sites More sharing options...
fenway Posted February 27, 2007 Share Posted February 27, 2007 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' ) Link to comment https://forums.phpfreaks.com/topic/40249-solved-interpreting-2-digit-years/#findComment-195377 Share on other sites More sharing options...
artacus Posted February 27, 2007 Author Share Posted February 27, 2007 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' Link to comment https://forums.phpfreaks.com/topic/40249-solved-interpreting-2-digit-years/#findComment-195397 Share on other sites More sharing options...
fenway Posted February 27, 2007 Share Posted February 27, 2007 You're probably right... I didn't test it very thoroughly. Link to comment https://forums.phpfreaks.com/topic/40249-solved-interpreting-2-digit-years/#findComment-195404 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.