SQL Server Converting Date, Time to UNIX Epoch time (Pre-SQL 2016)

Question

I am currently trying to convert SQL server (2008 R2 and 2012) audit logs from .sqlaudit file to csv/json/txt via PowerShell sqlcmd command. There is no issues doing this.
Something like this:
Invoke-sqlcmd -ServerInstance ...I -Query "SELECT * FROM sys.fn_get_audit_file ('\path\Audit-20210210-210446_F3B4AE48-04C4-4A14-AE30-F1AEB2AFF123_0_132580264066030000.sqlaudit',default,default);" | ConvertTo-Json -Depth 1 | out-file .....

However I need the date output in UNIX epoch time format (in milli sec).
I tried the datediff command however SELECT DATEDIFF(s,'1970-01-01',event_time) * 1000 does not work as this 13-digit value exceeds SQL integer limit.
I know form SQL 2016 there is big int, however older sql version does not have this.
Wondering what are the options?
I might try via PS on a different script - not sure how Win 2012 PS handled big int/conversion to big int.
Thanks

Answer 1

If you believe that bigint is not always available, then try something like this:

select cast(datediff(s, '1970-01-01', event_time) as varchar(11)) + '000'

Answer 2

Hi @shaikh shoaib ，

Welcome to Microsoft Q&A!

You could use cast() or convert() as below:

SELECT DATEDIFF(s,'1970-01-01',event_time) * convert(bigint, 1000)  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.