P3D Timestamp Convert Format

[jgarzaM6SUZ]

Hello,

I am trying to write a report via SQL to pull all the locked lines and also the timestamp column. I have been trying to research the timestamp format and have not been able to determine the format. Just seeing if anyone has had a run into the same situation and figure on how to convert it.

Thanks,

Jorge Garza

[MateusMaciel]

try this solution

https://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-...

[jgarzaM6SUZ]

@MateusMaciel , I have looked at several posts just like this one, but the source format needs to be known in order to convert to the proper format I need. Like in Data Manager it displays like "10/22/2020 1:15:18 PM" and in the table it shows as follows "637389873180923485". Nothing that I have tried seems to work.

Jorge Garza

[DVaquand]

Hello

Here is a link to find the date from the timestamp attribute

https://knowledge.autodesk.com/support/autocad-plant-3d/learn-explore/caas/sfdcarticles/sfdcarticles...

Cordialy

Dominique VAQUAND

www.dovaq.fr

[dave.wolfe · Accepted solution]

I used this formula in PowerQuery: #datetime(1,1,1,0,0,0)+#duration(0,0,0, [PnPTimestamp]/10000000)

[jgarzaM6SUZ · Accepted solution]

@dave.wolfe  Thanks I will actually try that, since I am using excel and power query for the data after I write the sql views.

I did notice that it’s using the scientific notation formation, will this formula resolve that?

thanks,

jorge garza

[dave.wolfe]

Yes, I created a custom column to hold the value.

[dave.wolfe]

FYI, if anyone cares, the value is an int 64 not a normal SQL date/time value, so it has to be handled in a special way. 

[jgarzaM6SUZ]

@dave.wolfe Yes, I do. Trying to determine what an int64 has to do/ handle for an cell entry? Might need to explain that to me in more detail sometime. Thanks fir the help.

thanks,

jorge garza

[jgarzaM6SUZ]

@dave.wolfe 

Any idea why the date time stamp would be off 6 hours. I have checked the sql server and the file server for date and time zone and its all correct.

[dave.wolfe]

It's probably storing in UTC.

[dinant]

I'm looking into the same. 

I found in the Autodesk help a excelfile to convert the timestamp to a readable format.

Not only is the hour off (But related to UTC it is correct)

But when i fill in the full years i get the year 3624 in stead of 2024.

In the new projects the timestamp starts with a 6, in older ones (2014/2015) the timestamp starts with a 1

That was related to 1970 (January 1) Excel is not supporting me a number for 1601 where this new timestamp is related to.

https://www.autodesk.com/support/technical/article/caas/sfdcarticles/sfdcarticles/How-to-convert-SQL...