XF 1.5 Birthdates lost in SMF to XF Import-- SQL to the rescue?

[leemy]

Birthdates were not properly imported coming from SMF to XF. Multiple users are reporting that their birthdates are wrong, and looking at the DB it looks like the month was brought over correctly, then the day was set to be 31 for everyone and the year 1900 for everyone. So if your birthday is 09/22/1980, it'll be in XF as 09/01/1900.

If users try to update their DOB, they're told in XF that it's set at account creation (this may be a permission thing)

I could use some help in using SQL to bring over birthdates. Here's the relevant info I have, i can copy the SMF table into my XF database:

SMF:
table: `smf_members`
user ID: `id_member` as mediumint(8), e.g. 124, 1900
DOB: `birthdate` as date e.g. 1980-08-30
(default value 0001-01-01. There are a few weird ones I dont know what SMF was doing but the year is 0004 or 1200 or 1800. So we should only import where year is >= 1900)

XF:
table: `xf_user_profile`
user ID: `user_id` int(10) (same user ID as SMF)
DOB:
`dob_day` tinyint(3)
`dob_month` tinyint(3)
`dob_year` tinyint(3)

I could use some help in getting the SQL together. Thanks!

 

[Mouth]

UPDATE 'xf_user_profile'
INNER JOIN 'smf_members' ON 'xf_user_profile.user_id' = 'smf_members.id_member'
SET 'xf_user_profile.dob_day' = DAY('smf_members.birthdate'), 'xf_user_profile.dob_month' = MONTH('smf_members.birthdate'), 'xf_user_profile.dob_year' = YEAR('smf_members.birthdate')

 

[leemy]

Thanks!! I thought I was going to have to use substring as I'm not familiar enough with date fields. For future reference of others, here is the SQL I used verbatim:

UPDATE xf_user_profile
INNER JOIN smf_members ON xf_user_profile.user_id = smf_members.id_member

SET xf_user_profile.dob_day = DAY(smf_members.birthdate), xf_user_profile.dob_month = MONTH(smf_members.birthdate), xf_user_profile.dob_year = YEAR(smf_members.birthdate)
where YEAR(Birthdate) > 1900