Integer date to readable dates

[Trekmp]

I currently work in ASP and am in the process of moving some
application across to PHP. At present I store dates in a database in
numerical format 38694, which is 08/12/2005. In ASP its very easy to
convert this value back into a date, but I cannot find a way to do this
in PHP.

Thanks for any help

[Ewoud Dronkert]

Trekmp wrote:

numerical format 38694, which is 08/12/2005.

How?

--
E. Dronkert

[Sjoerd]

"Now what is an Excel serial date number? 37477 is a serial date number
and is the number of days since 1-1-1900. 37477 actually translates to
Aug 9, 2002, the date of writing this article."

http://www.codeproject.com/datetime/exceldmy.asp

[Ewoud Dronkert]

Sjoerd wrote:

number of days since 1-1-1900

Of course! I missed a decimal, and thought: "Hmm what could it be, number
of days since 1995 somewhere?!"

Unfortunately mktime(0, 0, 0, 1, 38694, 1900) doesn't work (on Windows
anyway), so use an offset:

define('EPOCHINEXCEL', 25568); //01-01-1970 minus 1

function excel2unix($n) {
return mktime(0, 0, 0, 1, $n - EPOCHINEXCEL, 1970);
}

$myexceldate = 38694; //today
echo date('d-m-Y', excel2unix($myexceldate)); //prints 08-12-2005

You may want to extend the function to handle fractions (time of day).

--
E. Dronkert

[NC]

Trekmp wrote:

I currently work in ASP and am in the process of moving some
application across to PHP. At present I store dates in a database
in numerical format 38694, which is 08/12/2005. In ASP its very
easy to convert this value back into a date, but I cannot find a way
to do this in PHP.

First of all, you should have ways of converting the numerical
presentation into a human-readable date on the database level. In SQL
Server, you can use the CONVERT() function for that.

If for whatever reason this is not feasible (although I can't imagine
why), you must remember that PHP relies on Unix system libraries to
work with dates. So PHP is not well-suited to handling dates preceding
January 1, 1970. Assuming all your dates are after January 1, 1970,
here is a quick fix:

function MStoUnix ($MSdate) {
return round(($MSdata - 25569) * 24 * 60 * 60);
}

This function converts a Microsoft "timestamp" (number of days since
January 1, 1900) into a Unix timestamp (number of seconds since January
1, 1970). Then you can format the resulting timestamp using the date()
function. For example, this snippet:

echo date('Y-m-d', MStoUnix(38694));

would output "2005-12-08".

Cheers,
NC

[Ewoud Dronkert]

NC wrote:

Assuming all your dates are after January 1, 1970,
here is a quick fix:

function MStoUnix ($MSdate) {
return round(($MSdata - 25569) * 24 * 60 * 60);
}

This function converts a Microsoft "timestamp" (number of days since
January 1, 1900) into a Unix timestamp (number of seconds since January
1, 1970). Then you can format the resulting timestamp using the date()
function. For example, this snippet:

echo date('Y-m-d', MStoUnix(38694));

would output "2005-12-08".

Oh really!

--
E. Dronkert

[Ewoud Dronkert]

Ewoud Dronkert wrote:

NC wrote:

return round(($MSdata - 25569) * 24 * 60 * 60);

Oh really!

Sorry, having never dealt with Exceldates before (in php), I was rather
chuffed with my almost identical solution I posted earlier. But this part
of yours(?) is better because it deals with fractions in one step.

--
E. Dronkert