Command to get milliseconds out of datetime stamp with milliseconds

Tina Douglass · 2025-04-23T14:17:27+00:00

There was an error rendering this rich post.

I need to convert a datetimestamp to the equivilent EPOCH value. We were going to do a datediff and then tack on the Milliseconds at the end, but i am not sure how to extrapolate the Milliseconds. Is anyone aware of how to convert datetimestamp back to the epock value or get milliseconds from a datetimestamp?

Find more posts tagged with

Accepted answers

Diane McElhiney

SELECT DATEDIFF(ms, '1970-01-01 00:00:00', GETUTCDATE())

The above executes in Compass. You can try replacing GETUTCDATE() with the datetimestamp column from the source object.

All comments

Diane McElhiney

SELECT DATEDIFF(ms, '1970-01-01 00:00:00', GETUTCDATE())

The above executes in Compass. You can try replacing GETUTCDATE() with the datetimestamp column from the source object.

Tina Douglass

thank you diane. i think that worked

Tina Douglass

spoke to soon. it works in Compass, but i can't get to work in select in Birst or in the select in connector. Will keep working on it.

Diane McElhiney

I used this example in the Birst Infor Data Lake connector

SELECT DATEDIFF(ms, '1970-01-01 00:00:00', GETUTCDATE()), UNIX_TIMESTAMP (GETUTCDATE()), GETUTCDATE()

I'm receiving the expected results. Whatever SELECT statement is used within Data Fabric Compass is 1:1 within a Birst Data Lake Connector QBO.

Tina Douglass

hmm. okay, let me try again.

Kevin Heiman

Some other examples:


select GETUTCDATE() as "UTC Date"

, DATE_FORMAT(GETUTCDATE(), 'yyyy-MM-ddd hh:mm:ss.SSS') AS "UTC Formatted"

, CAST(DATE_FORMAT(GETUTCDATE(), 'yyyy-MM-ddd hh:mm:ss.SSS') AS VARCHAR) AS "UTC Formatted varchar"

, DATEDIFF(ms, '1970-01-01 00:00:00', GETUTCDATE()) AS "Unix EPOC"

, UNIX_TIMESTAMP (GETUTCDATE())  AS "Unix_EPOCH3"

Tina Douglass

Thanks. I believe i now have it running in QBO. Thank you both!

Kevin Heiman

I meant to add the documentation link. https://docs.infor.com/inforos/2025.x/en-us/useradminlib_cloud/default.html?helpcontent=datafabrug/dzi1631199611238.html

I am also pasting a similar post from a few years back that was in the old Infor OS community.
==========================

Online Kevin Heiman over 3 years ago in reply to Kevin Heiman

On the topic of EPOCH timestamp conversion, here are several ways to convert depending on your needs.

select top 5 LMTS as "LMTS EPOCH", CAST(DATEADD(SECOND, LMTS/1000,'1970-01-01') AS TIMESTAMP) as LMTS_TimeStamp, CAST(DATEADD(SECOND, LMTS/1000,'1970-01-01') AS DATETIME) as LMTS_DateTime, DATEADD(SECOND, LMTS/1000,'1970-01-01') as "LMTS DateTime DATEADD only", DATEADD(MILLISECOND, LMTS % 1000, DATEADD(SECOND, LMTS/1000, '1970-01-01')) as "Last Mod w Millisec", substring(to_iso8601(DATEADD(MILLISECOND, LMTS % 1000, DATEADD(SECOND, LMTS/1000, '1970-01-01'))), 1, 10) as "Last Mod ISO Date Only", convert(varchar, DATEADD(MILLISECOND, LMTS % 1000, DATEADD(SECOND, LMTS/1000, '1970-01-01')) , 112) as "Last Mod YYMMDD Only" from MITMAS

`LMTS EPOCH`	`LMTS_TimeStamp`	`LMTS_DateTime`	`LMTS DateTime DATEADD only`	`Last Mod w Millisec`	`Last Mod ISO Date Only`	`Last Mod YYMMDD Only`
`1631908400996`	`2021-09-17T19:53:20.000Z`	`2021-09-17T19:53:20.000Z`	`2021-09-17T19:53:20.000Z`	`2021-09-17T19:53:20.996Z`	`9/17/2021`	`20210917`
`1629074458693`	`2021-08-16T00:40:58.000Z`	`2021-08-16T00:40:58.000Z`	`2021-08-16T00:40:58.000Z`	`2021-08-16T00:40:58.693Z`	`8/16/2021`	`20210816`
`1629074458895`	`2021-08-16T00:40:58.000Z`	`2021-08-16T00:40:58.000Z`	`2021-08-16T00:40:58.000Z`	`2021-08-16T00:40:58.895Z`	`8/16/2021`	`20210816`
`1634755514363`	`2021-10-20T18:45:14.000Z`	`2021-10-20T18:45:14.000Z`	`2021-10-20T18:45:14.000Z`	`2021-10-20T18:45:14.363Z`	`10/20/2021`	`20211020`
`1604605647977`	`2020-11-05T19:47:27.000Z`	`2020-11-05T19:47:27.000Z`	`2020-11-05T19:47:27.000Z`	`2020-11-05T19:47:27.977Z`	`11/5/2020`	`20201105`