Solve Date format problem for Excel into SQL Server via PHP

We can upload data in database using excel easily but whenever we will upload any kind of date through excel than the uploaded date unrecognized sometimes.

This is because of different supported date formats by different versions of word editors. Like MS Word 2003 supports “Day/Month/Year” (dd/mm/yy) format, MS Word 2007 supports “Month/Day/Year” (mm/dd/yy) format and Open Office supports “Month Name/Day,Year”(MM/dd,yy).

So there are some difficulties while we use some default function and of course we can’t bind our user to use some kind of specific software.

So the solution for this problem is that, we have to convert the format of date from date to number using option “Format Cells” so our date will display some kind of numeric data on the cell.

This is because excel use a simple number formatting to read dates like 1/1/1900 = 1.00 and 16/1/2012 = 40924.00. So whenever we have to upload dates using excel we have to convert it into number and then upload it and just before the insert query of database we use a function which is listed below to convert the number format into date format which support by database.

function excel_number_to_date($num)

{

$num=$num-25570;              //this is because php date function work only for date after 1/1/1970

return addday(‘1970/01/02’,$num);

}

The static no. 25570 denoted a date of 1/1/1970. Because while we upload data in MySQL than it will not read date which is older than 1/1/1970. Thant’s why I have to subtract this particular no. IT totally depends on type of database and addday function is listed below.

function addday($dat,$days)                                                      //’$dat’ will be in ‘YYYY/mm/dd’ format

{

$dat=str_replace(“/”,”-“,$dat);

$dat=date(“Y-m-d”,strtotime($dat));

return date(“Y-m-d”,strtotime($days.’ days’,strtotime($dat)));

}