SQL date as INTEGER to PHP datetime format

Question

I have problem to convert data saved as INTEGER in mysql server to display datetime format.

I have access to sql server view using php.

Date like:

2008-02-29 00:00:00.000

i saved like: 75668 (integer)

How to format

to

date("Y-m-d H:i:s")

? and

date("Y-m-d H:i:s")

to this format (like 75668)?

I see that in sql server it's done like:

cast (a.DATA - 36163 as datetime) DATA_W

How can I format this value using php?

In structure this table I have :

[DATA] [dbo].[T_DATA_TYP] NULL,

this column is create bu custom type. Type declaration is :

CREATE TYPE [dbo].[T_DATA_TYP] FROM [int] NULL

What is the relationship between 75668 and 29th February 2008? Is there some kind of formula? Days since a base date? — Mark Baker
– Mark Baker, Commented Mar 9, 2016 at 17:50
why are you saving date time as integer data type ?? There is datatype for datetime in mysql database . — Drudge Rajen
– Drudge Rajen, Commented Mar 9, 2016 at 17:51
This is not my database (it's programs db) In sql server column DATA is saved by custom type CREATE TYPE [dbo].[T_DATA_TYP] FROM [int] NULL — marczak
– marczak, Commented Mar 9, 2016 at 17:53
In structure this table I have : [DATA] [dbo].[T_DATA_TYP] NULL, this column is create bu custom type. Type declaration is : CREATE TYPE [dbo].[T_DATA_TYP] FROM [int] NULL — marczak
– marczak, Commented Mar 9, 2016 at 18:00

Mark Baker · Accepted Answer · 2016-03-09 19:10:57Z

1

This looks like it's a variant of an MS Excel serialized timestamp, which is a count of the days since 31st December 1899.... if that is the case, then you can convert it to a unix timestamp using a function like:

function ExcelToUnixTimestamp($dateValue = 0)
{
    $excelBaseDate = 25567;

    // Perform conversion
    if ($dateValue >= 1) {
        $utcDays = $dateValue - $excelBaseDate;
        $returnValue = round($utcDays * 86400);
        if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
            $returnValue = (integer) $returnValue;
        }
    } else {
        $hours = round($dateValue * 24);
        $mins = round($dateValue * 1440) - round($hours * 60);
        $secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
        $returnValue = (integer) gmmktime($hours, $mins, $secs);
    }

    return $returnValue;
}

$weirdTimestamp = 75668;
$unixTimestamp = ExcelToUnixTimestamp($weirdTimestamp - 36163);
echo date('Y-m-d H:i:s', $unixTimestamp);

answered Mar 9, 2016 at 19:10

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Grzegorz J Over a year ago

There isn't MS Excel serialize timestamp. It's clarion format.

Grzegorz J · Accepted Answer · 2020-11-12 08:54:00Z

0

This date is in CLARION format.

user this: c#: var xx = new DateTime(1800, 12, 28).AddDays(75668);

in sql: DECLARE @SqlDt DATETIME SET @SqlDt = DateAdd(day, 75668, '1800-12-28') SELECT @SqlDt AS 'SQL Date Time'

answered Nov 12, 2020 at 8:54

Grzegorz J

5514 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL date as INTEGER to PHP datetime format

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related