![](https://cdn.userecho.com/characters/67.png?1)
How do I convert the stored bigintvalue to a datetime?
I'm doing some data analysis and need to get a datetime value out of the IdB database where it is stored as a BigIntValue. How do I convert this to a readable format? I found a select query that would convert Unix time but it didn't work on these values (Arithmetic overflow error). Anyway the IdB values look different to those examples.
Answer
![](https://cdn.userecho.com/characters/65.png?1)
They are .NET ticks (UTC). TicksToTime.ps1
However, keep in mind that operations directly against the database are not supported and could also change/break between versions.
![](https://cdn.userecho.com/characters/67.png?1)
Thanks Adam! Don't worry, this is a one-off to help prioritise some data clean-up that needs to happen.
![](https://cdn.userecho.com/characters/67.png?1)
To summarise process:
- export values out to CSV
- the following powershell will convert to datetime:
foreach ($row in $CSV)
{
$ticks = [long]$row.BigIntValue
get-date $ticks
}
Customer support service by UserEcho
They are .NET ticks (UTC). TicksToTime.ps1
However, keep in mind that operations directly against the database are not supported and could also change/break between versions.