Imported birthdate problem

[Dv_]

Hi !

I've just imported my phpBB 2.0.21 forum to vBulletin 3.6.0 thanks to Impex.

I used to have a birthday mod on my phpbb forum, as it is not a standard feature on it.
This date was stock on the users table, the field was called user_birthday and had the int(11) type.

However, it looks like the date now showing on the vBulletin forum is wrong, to be more precise, there is a 1 day difference : someone born on the 1st of january is now registred with a birthday date of 2nd of january on the vb forum !

I found a birthday field on vb's user table, but its type is varchar(10) !

Is there a way to re-assign the correct birth date to my members (with an SQL command ?) or is the only trick will be to alter the phpbb user table and try to import it again ?

Thanks for your help ! ;)

[Jerry]

In vBulletin birthday is a char(10) though birthday_search is date.

You would have to update both in the format :

birthday = "{$month}-{$day}-{$year}"
birthday_search = "{$year}-{$month}-{$day}"

The phpBB2 importer, imports the birthday from the common hack, I'd take a 2nd look at your source data, as this is a very common hack for phpBB2 that gets imported and this is the first error I've seen with it :confused:

[Dv_]

Hi Jerry, thanks for your answer !

I have the Birthday Mod v1.5.7 installed on the phpBB version of my forum.

When I look on the databases, as an example, for one user (me :rolleyes: , born on the 6th of February 1974, registred on that forum on January 22nd 2004) here is what I can find (these are of course sample of the tables):

phpBB "users" table :
username varchar(25) = dv
user_regdate int(11) = 1074783569
user_birthday int(11) = 1497

--> phpBB forum shows correctly both join date and date of birth

vBulletin "user" table :
username varchar(100) = dv
joindate int(10) unsigned = 1074783540
birthday varchar(10) = 02-07-1974
birthday_search date = 1974-02-07

--> the right join date is displayed on the forum but the date of birth had a problem during the import process. :confused:

[Lats]

If they are ALL out, by one day, try this query...

UPDATE
user
SET
birthday_search = DATE_SUB(birthday_search, INTERVAL 1 DAY),
birthday = concat(mid(birthday_search,6,2),'-',right(birthday_search,2),'-', left(birthday_search,4))

[Jerry]

Most strange as the code that deciphers the date is this :



$user['user_birthday'] = '1497';

$bd = $user['user_birthday']*86400+86400;
$year = @date("Y", $bd); // Errors off for windows
$month = @date("m", $bd);
$day = @date("d", $bd);
echo "<br>birthday : {$month}-{$day}-{$year}";
echo "<br>birthday_search : {$year}-{$month}-{$day}";


Which gives this :




birthday : 02-06-1974
birthday_search : 1974-02-06


While Lat's SQL should fix the target, it would be nice to know what is going on.

[Dv_]

Yes ! The query solved the problem, thanks a lot Lats ! ;)

As far as I can remember, I made a fresh install of vBulletin v3.6.0 and made very few things before the import process, but I'm sure I changed the time zone to set vBulletin to UTC +1 (CET) and set the date format to d-m-y before processing. Can it be the origin of the problem ?

[Jerry]

Yes ! The query solved the problem, thanks a lot Lats ! ;)

As far as I can remember, I made a fresh install of vBulletin v3.6.0 and made very few things before the import process, but I'm sure I changed the time zone to set vBulletin to UTC +1 (CET) and set the date format to d-m-y before processing. Can it be the origin of the problem ?

Shouldn't of, though ImpEx is designed to import into a default schema and settings.

[Dv_]

Ok, here is another problem, should be linked to the fact the birth dates were changed on the database: on the home page of my forum, today's birthdays are now the one from yesterday (I mean, members with August 22 as date of birth are listed !).

When I click on the birthday cake icon on the left, the calendar is opening correctly to today's page with members having August 23 as date of birth on it.

I know there is a "user_next_birthday_greeting" entry on the phpBB user table, is there something similar on vBulletin that must be updated too ?


Edit : I turned off the "Display Today's Birthdays" option on the "forum home page options" page, and turned it on again, seems it has solved the problem ! ;)