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]

Re: Integer date to readable dates

Trekmp wrote:[color=blue]
> numerical format 38694, which is 08/12/2005.[/color]

How?

--
E. Dronkert

[Sjoerd]

Re: Integer date to readable dates

"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]

Re: Integer date to readable dates

Sjoerd wrote:[color=blue]
> number of days since 1-1-1900[/color]

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('EPOCHIN EXCEL', 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($mye xceldate)); //prints 08-12-2005

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

--
E. Dronkert

[NC]

Re: Integer date to readable dates

Trekmp wrote:[color=blue]
>
> 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.[/color]

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]

Re: Integer date to readable dates

NC wrote:[color=blue]
> 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".[/color]

Oh really!

--
E. Dronkert

[Ewoud Dronkert]

Re: Integer date to readable dates

Ewoud Dronkert wrote:[color=blue]
> NC wrote:[color=green]
>> return round(($MSdata - 25569) * 24 * 60 * 60);[/color]
>
> Oh really![/color]

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